<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>SQL必知必会题解 | ShuangChenYue</title>
    <meta name="generator" content="VuePress 1.9.9">
    <link rel="icon" href="https://cdn.jsdelivr.net/gh/cmty256/imgs-blog@main/logo/白云.38zbldnhh180.jpg">
    <meta name="description" content="满招损，谦受益">
    <meta name="keywords" content="专注于Cpp语言的旅行者">
    
    <link rel="preload" href="/assets/css/0.styles.3fffbd7d.css" as="style"><link rel="preload" href="/assets/js/app.ef25b098.js" as="script"><link rel="preload" href="/assets/js/2.4615a819.js" as="script"><link rel="preload" href="/assets/js/69.6d87bcc0.js" as="script"><link rel="prefetch" href="/assets/js/10.5712a6b9.js"><link rel="prefetch" href="/assets/js/11.1fed716c.js"><link rel="prefetch" href="/assets/js/12.4672093d.js"><link rel="prefetch" href="/assets/js/13.cda96393.js"><link rel="prefetch" href="/assets/js/14.0924c9b3.js"><link rel="prefetch" href="/assets/js/15.443e1f82.js"><link rel="prefetch" href="/assets/js/16.554bf912.js"><link rel="prefetch" href="/assets/js/17.994ca8e7.js"><link rel="prefetch" href="/assets/js/18.4879414d.js"><link rel="prefetch" href="/assets/js/19.f66c5ece.js"><link rel="prefetch" href="/assets/js/20.79449f9c.js"><link rel="prefetch" href="/assets/js/21.0b51ee17.js"><link rel="prefetch" href="/assets/js/22.14f9241e.js"><link rel="prefetch" href="/assets/js/23.fa858e5d.js"><link rel="prefetch" href="/assets/js/24.a420d3d2.js"><link rel="prefetch" href="/assets/js/25.fee08d36.js"><link rel="prefetch" href="/assets/js/26.92805480.js"><link rel="prefetch" href="/assets/js/27.5c60f91f.js"><link rel="prefetch" href="/assets/js/28.2bbf1597.js"><link rel="prefetch" href="/assets/js/29.05fbd423.js"><link rel="prefetch" href="/assets/js/3.7b56aee3.js"><link rel="prefetch" href="/assets/js/30.54f725db.js"><link rel="prefetch" href="/assets/js/31.3e5c530d.js"><link rel="prefetch" href="/assets/js/32.f8ecda14.js"><link rel="prefetch" href="/assets/js/33.3bef3da7.js"><link rel="prefetch" href="/assets/js/34.0e1b1990.js"><link rel="prefetch" href="/assets/js/35.21acae61.js"><link rel="prefetch" href="/assets/js/36.744eb2f7.js"><link rel="prefetch" href="/assets/js/37.077678f2.js"><link rel="prefetch" href="/assets/js/38.d2c19552.js"><link rel="prefetch" href="/assets/js/39.d873f04b.js"><link rel="prefetch" href="/assets/js/4.8844c7c6.js"><link rel="prefetch" href="/assets/js/40.a9c0394e.js"><link rel="prefetch" href="/assets/js/41.bd1b72f7.js"><link rel="prefetch" href="/assets/js/42.8c5ba50f.js"><link rel="prefetch" href="/assets/js/43.aac76280.js"><link rel="prefetch" href="/assets/js/44.2601ff73.js"><link rel="prefetch" href="/assets/js/45.2802dd41.js"><link rel="prefetch" href="/assets/js/46.3a872e80.js"><link rel="prefetch" href="/assets/js/47.fb914cfa.js"><link rel="prefetch" href="/assets/js/48.4ce4214b.js"><link rel="prefetch" href="/assets/js/49.968e0b60.js"><link rel="prefetch" href="/assets/js/5.d85ca603.js"><link rel="prefetch" href="/assets/js/50.4cee1e39.js"><link rel="prefetch" href="/assets/js/51.5cad3040.js"><link rel="prefetch" href="/assets/js/52.1612ad39.js"><link rel="prefetch" href="/assets/js/53.e9cf7aec.js"><link rel="prefetch" href="/assets/js/54.2821cc0e.js"><link rel="prefetch" href="/assets/js/55.6f8ddb85.js"><link rel="prefetch" href="/assets/js/56.58b793ba.js"><link rel="prefetch" href="/assets/js/57.3d08056f.js"><link rel="prefetch" href="/assets/js/58.880ee89f.js"><link rel="prefetch" href="/assets/js/59.987dab20.js"><link rel="prefetch" href="/assets/js/6.b5c87a76.js"><link rel="prefetch" href="/assets/js/60.6089e8d9.js"><link rel="prefetch" href="/assets/js/61.2f20e8a2.js"><link rel="prefetch" href="/assets/js/62.03592460.js"><link rel="prefetch" href="/assets/js/63.77002dd4.js"><link rel="prefetch" href="/assets/js/64.180619d0.js"><link rel="prefetch" href="/assets/js/65.2750f9b6.js"><link rel="prefetch" href="/assets/js/66.c696b1af.js"><link rel="prefetch" href="/assets/js/67.03363cd9.js"><link rel="prefetch" href="/assets/js/68.163161cc.js"><link rel="prefetch" href="/assets/js/7.70b0614b.js"><link rel="prefetch" href="/assets/js/70.53173d63.js"><link rel="prefetch" href="/assets/js/71.5acaf78b.js"><link rel="prefetch" href="/assets/js/72.e5473318.js"><link rel="prefetch" href="/assets/js/73.fca88a39.js"><link rel="prefetch" href="/assets/js/74.d45cfae5.js"><link rel="prefetch" href="/assets/js/75.81660c22.js"><link rel="prefetch" href="/assets/js/76.3e7aeba1.js"><link rel="prefetch" href="/assets/js/77.2e4da6e0.js"><link rel="prefetch" href="/assets/js/78.7f7d7694.js"><link rel="prefetch" href="/assets/js/79.f60ae352.js"><link rel="prefetch" href="/assets/js/8.ecc019cf.js"><link rel="prefetch" href="/assets/js/80.3a10fa1d.js"><link rel="prefetch" href="/assets/js/81.72805d9a.js"><link rel="prefetch" href="/assets/js/82.c1d3f9b6.js"><link rel="prefetch" href="/assets/js/83.9014695a.js"><link rel="prefetch" href="/assets/js/84.4602494c.js"><link rel="prefetch" href="/assets/js/85.f2189c96.js"><link rel="prefetch" href="/assets/js/86.b6a21db1.js"><link rel="prefetch" href="/assets/js/87.9be7c553.js"><link rel="prefetch" href="/assets/js/88.562483e1.js"><link rel="prefetch" href="/assets/js/89.336befe6.js"><link rel="prefetch" href="/assets/js/9.4b46b767.js"><link rel="prefetch" href="/assets/js/90.e916d603.js"><link rel="prefetch" href="/assets/js/91.873db624.js"><link rel="prefetch" href="/assets/js/92.9072026b.js"><link rel="prefetch" href="/assets/js/93.0baf9593.js"><link rel="prefetch" href="/assets/js/94.8655b40d.js"><link rel="prefetch" href="/assets/js/95.bd6a4d6c.js"><link rel="prefetch" href="/assets/js/96.943e9bb3.js"><link rel="prefetch" href="/assets/js/97.ea0c9e4a.js">
    <link rel="stylesheet" href="/assets/css/0.styles.3fffbd7d.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="https://cdn.jsdelivr.net/gh/cmty256/imgs-blog@main/logo/白云.38zbldnhh180.jpg" alt="ShuangChenYue" class="logo"> <span class="site-name can-hide">ShuangChenYue</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="CPP语言" class="dropdown-title"><!----> <span class="title" style="display:;">CPP语言</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/c5bdd8/" class="nav-link">Cpp之旅</a></li><li class="dropdown-item"><!----> <a href="/pages/279e62/" class="nav-link">Cpp专栏</a></li><li class="dropdown-item"><!----> <a href="/pages/801755/" class="nav-link">Effective_CPP</a></li><li class="dropdown-item"><!----> <a href="/pages/6b2468/" class="nav-link">muduo网络库</a></li><li class="dropdown-item"><!----> <a href="/pages/5f8c9f/" class="nav-link">Unix环境高级编程</a></li><li class="dropdown-item"><!----> <a href="/pages/3f1d21/" class="nav-link">Cpp提高编程</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="计算机基础" class="dropdown-title"><!----> <span class="title" style="display:;">计算机基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/7b1cb2/" class="nav-link">计算机网络</a></li><li class="dropdown-item"><!----> <a href="/pages/6048a8/" class="nav-link">操作系统</a></li><li class="dropdown-item"><!----> <a href="/pages/3b34ba/" class="nav-link">数据结构</a></li><li class="dropdown-item"><!----> <a href="/pages/412fe7/" class="nav-link">Linux</a></li><li class="dropdown-item"><!----> <a href="/pages/2dcfa1/" class="nav-link">算法</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><!----> <span class="title" style="display:;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/efa3f2/" class="nav-link">基础篇</a></li><li class="dropdown-item"><!----> <a href="/pages/ccc445/" class="nav-link">MySql</a></li><li class="dropdown-item"><!----> <a href="/pages/54616e/" class="nav-link">Redis</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="系统设计" class="dropdown-title"><!----> <span class="title" style="display:;">系统设计</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/793d0a/" class="nav-link">权限校验</a></li><li class="dropdown-item"><!----> <a href="/pages/73ddd7/" class="nav-link">设计模式</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="开发日常" class="dropdown-title"><!----> <span class="title" style="display:;">开发日常</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/71f6ae/" class="nav-link">Git知识总结</a></li><li class="dropdown-item"><!----> <a href="/pages/777b8a/" class="nav-link">nvm使用小结</a></li><li class="dropdown-item"><!----> <a href="/pages/ee770e/" class="nav-link">虚拟机固定 IP 地址</a></li><li class="dropdown-item"><!----> <a href="/pages/e472d1/" class="nav-link">随笔（持续更新）</a></li><li class="dropdown-item"><!----> <a href="/pages/411aa4/" class="nav-link">VScode 插件 CodeGeeX 使用教程</a></li><li class="dropdown-item"><!----> <a href="/pages/0d525d/" class="nav-link">KylinV10 将项目上传至 Github教程</a></li><li class="dropdown-item"><!----> <a href="/pages/ef40f0/" class="nav-link">KylinV10 安装 MySQL 教程（可防踩雷）</a></li><li class="dropdown-item"><!----> <a href="/pages/f8640c/" class="nav-link">个人博客代码推送教程</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端学习" class="dropdown-title"><!----> <span class="title" style="display:;">前端学习</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/99897f/" class="nav-link">HTML与CSS</a></li><li class="dropdown-item"><!----> <a href="/pages/51542d/" class="nav-link">JS学习</a></li><li class="dropdown-item"><!----> <a href="/pages/803f9d/" class="nav-link">Vue3入门</a></li><li class="dropdown-item"><!----> <a href="/pages/ca4cfb/" class="nav-link">Vue3进阶</a></li><li class="dropdown-item"><!----> <a href="/pages/50e8d3/" class="nav-link">黑马Vue3</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="项目笔记" class="dropdown-title"><!----> <span class="title" style="display:;">项目笔记</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/08dfe9/" class="nav-link">ip_file_hook</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="面经" class="dropdown-title"><!----> <span class="title" style="display:;">面经</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/d69946/" class="nav-link">虎牙C++技术面经</a></li><li class="dropdown-item"><!----> <a href="/pages/29251d/" class="nav-link">金山一面复习</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其它" class="dropdown-title"><!----> <span class="title" style="display:;">其它</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/fa256e/" class="nav-link">博客搭建</a></li><li class="dropdown-item"><!----> <a href="/pages/335531/" class="nav-link">网站收藏箱</a></li></ul></div></div> <!----></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><!----> <nav class="nav-links"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="CPP语言" class="dropdown-title"><!----> <span class="title" style="display:;">CPP语言</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/c5bdd8/" class="nav-link">Cpp之旅</a></li><li class="dropdown-item"><!----> <a href="/pages/279e62/" class="nav-link">Cpp专栏</a></li><li class="dropdown-item"><!----> <a href="/pages/801755/" class="nav-link">Effective_CPP</a></li><li class="dropdown-item"><!----> <a href="/pages/6b2468/" class="nav-link">muduo网络库</a></li><li class="dropdown-item"><!----> <a href="/pages/5f8c9f/" class="nav-link">Unix环境高级编程</a></li><li class="dropdown-item"><!----> <a href="/pages/3f1d21/" class="nav-link">Cpp提高编程</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="计算机基础" class="dropdown-title"><!----> <span class="title" style="display:;">计算机基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/7b1cb2/" class="nav-link">计算机网络</a></li><li class="dropdown-item"><!----> <a href="/pages/6048a8/" class="nav-link">操作系统</a></li><li class="dropdown-item"><!----> <a href="/pages/3b34ba/" class="nav-link">数据结构</a></li><li class="dropdown-item"><!----> <a href="/pages/412fe7/" class="nav-link">Linux</a></li><li class="dropdown-item"><!----> <a href="/pages/2dcfa1/" class="nav-link">算法</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><!----> <span class="title" style="display:;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/efa3f2/" class="nav-link">基础篇</a></li><li class="dropdown-item"><!----> <a href="/pages/ccc445/" class="nav-link">MySql</a></li><li class="dropdown-item"><!----> <a href="/pages/54616e/" class="nav-link">Redis</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="系统设计" class="dropdown-title"><!----> <span class="title" style="display:;">系统设计</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/793d0a/" class="nav-link">权限校验</a></li><li class="dropdown-item"><!----> <a href="/pages/73ddd7/" class="nav-link">设计模式</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="开发日常" class="dropdown-title"><!----> <span class="title" style="display:;">开发日常</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/71f6ae/" class="nav-link">Git知识总结</a></li><li class="dropdown-item"><!----> <a href="/pages/777b8a/" class="nav-link">nvm使用小结</a></li><li class="dropdown-item"><!----> <a href="/pages/ee770e/" class="nav-link">虚拟机固定 IP 地址</a></li><li class="dropdown-item"><!----> <a href="/pages/e472d1/" class="nav-link">随笔（持续更新）</a></li><li class="dropdown-item"><!----> <a href="/pages/411aa4/" class="nav-link">VScode 插件 CodeGeeX 使用教程</a></li><li class="dropdown-item"><!----> <a href="/pages/0d525d/" class="nav-link">KylinV10 将项目上传至 Github教程</a></li><li class="dropdown-item"><!----> <a href="/pages/ef40f0/" class="nav-link">KylinV10 安装 MySQL 教程（可防踩雷）</a></li><li class="dropdown-item"><!----> <a href="/pages/f8640c/" class="nav-link">个人博客代码推送教程</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端学习" class="dropdown-title"><!----> <span class="title" style="display:;">前端学习</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/99897f/" class="nav-link">HTML与CSS</a></li><li class="dropdown-item"><!----> <a href="/pages/51542d/" class="nav-link">JS学习</a></li><li class="dropdown-item"><!----> <a href="/pages/803f9d/" class="nav-link">Vue3入门</a></li><li class="dropdown-item"><!----> <a href="/pages/ca4cfb/" class="nav-link">Vue3进阶</a></li><li class="dropdown-item"><!----> <a href="/pages/50e8d3/" class="nav-link">黑马Vue3</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="项目笔记" class="dropdown-title"><!----> <span class="title" style="display:;">项目笔记</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/08dfe9/" class="nav-link">ip_file_hook</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="面经" class="dropdown-title"><!----> <span class="title" style="display:;">面经</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/d69946/" class="nav-link">虎牙C++技术面经</a></li><li class="dropdown-item"><!----> <a href="/pages/29251d/" class="nav-link">金山一面复习</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其它" class="dropdown-title"><!----> <span class="title" style="display:;">其它</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/fa256e/" class="nav-link">博客搭建</a></li><li class="dropdown-item"><!----> <a href="/pages/335531/" class="nav-link">网站收藏箱</a></li></ul></div></div> <!----></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>基础</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/pages/efa3f2/" class="sidebar-link">数据库基础知识小结</a></li><li><a href="/pages/54fc03/" class="sidebar-link">NoSQL基础知识小结</a></li><li><a href="/pages/126a5a/" class="sidebar-link">字符集详解</a></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><p class="sidebar-heading open"><span>SQL</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/pages/ed0fa0/" class="sidebar-link">SQL语法基础小结</a></li><li><a href="/pages/def622/" aria-current="page" class="active sidebar-link">SQL必知必会题解</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/pages/def622/#检索数据" class="sidebar-link">检索数据</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#从-customers-表中检索所有的-id" class="sidebar-link">从 Customers 表中检索所有的 ID</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#检索并列出已订购产品的清单" class="sidebar-link">检索并列出已订购产品的清单⭐️</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#检索所有列" class="sidebar-link">检索所有列</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/def622/#排序检索数据" class="sidebar-link">排序检索数据</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#检索顾客名称并且排序" class="sidebar-link">检索顾客名称并且排序</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#对顾客-id-和日期排序" class="sidebar-link">对顾客 ID 和日期排序</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#按照数量和价格排序" class="sidebar-link">按照数量和价格排序⭐️</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#检查-sql-语句" class="sidebar-link">检查 SQL 语句</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/def622/#过滤数据" class="sidebar-link">过滤数据</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回固定价格的产品" class="sidebar-link">返回固定价格的产品</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回更高价格的产品" class="sidebar-link">返回更高价格的产品</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回产品并且按照价格排序" class="sidebar-link">返回产品并且按照价格排序⭐️</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回更多的产品" class="sidebar-link">返回更多的产品⭐️</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/def622/#高级数据过滤" class="sidebar-link">高级数据过滤</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#检索供应商名称" class="sidebar-link">检索供应商名称</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#检索并列出已订购产品的清单-2" class="sidebar-link">检索并列出已订购产品的清单⭐️</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回所有价格在-3-美元到-6-美元之间的产品的名称和价格" class="sidebar-link">返回所有价格在 3 美元到 6 美元之间的产品的名称和价格</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#纠错-2" class="sidebar-link">纠错 2</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/def622/#用通配符进行过滤" class="sidebar-link">用通配符进行过滤</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#检索产品名称和描述-一" class="sidebar-link">检索产品名称和描述（一）⭐️</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#检索产品名称和描述-二" class="sidebar-link">检索产品名称和描述（二）⭐️</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#检索产品名称和描述-三" class="sidebar-link">检索产品名称和描述（三）⭐️</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#检索产品名称和描述-四" class="sidebar-link">检索产品名称和描述（四）</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/def622/#创建计算字段" class="sidebar-link">创建计算字段</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#别名" class="sidebar-link">别名</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#打折" class="sidebar-link">打折⭐️</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/def622/#使用函数处理数据" class="sidebar-link">使用函数处理数据</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#顾客登录名" class="sidebar-link">顾客登录名⭐️</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回-2020-年-1-月的所有订单的订单号和订单日期" class="sidebar-link">返回 2020 年 1 月的所有订单的订单号和订单日期⭐️</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/def622/#汇总数据" class="sidebar-link">汇总数据</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#确定已售出产品的总数" class="sidebar-link">确定已售出产品的总数</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#确定已售出产品项-br01-的总数" class="sidebar-link">确定已售出产品项 BR01 的总数</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#确定-products-表中价格不超过-10-美元的最贵产品的价格" class="sidebar-link">确定 Products 表中价格不超过 10 美元的最贵产品的价格</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/def622/#分组数据" class="sidebar-link">分组数据</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回每个订单号各有多少行数" class="sidebar-link">返回每个订单号各有多少行数</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#每个供应商成本最低的产品" class="sidebar-link">每个供应商成本最低的产品</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回订单数量总和不小于-100-的所有订单的订单号" class="sidebar-link">返回订单数量总和不小于 100 的所有订单的订单号</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#计算总和" class="sidebar-link">计算总和</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#纠错-3" class="sidebar-link">纠错 3⭐️</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/def622/#使用子查询" class="sidebar-link">使用子查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回购买价格为-10-美元或以上产品的顾客列表" class="sidebar-link">返回购买价格为 10 美元或以上产品的顾客列表</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#确定哪些订单购买了-prod-id-为-br01-的产品-一" class="sidebar-link">确定哪些订单购买了 prod_id 为 BR01 的产品（一）</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回购买-prod-id-为-br01-的产品的所有顾客的电子邮件-一" class="sidebar-link">返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回每个顾客不同订单的总金额" class="sidebar-link">返回每个顾客不同订单的总金额⭐️</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#从-products-表中检索所有的产品名称以及对应的销售总数" class="sidebar-link">从 Products 表中检索所有的产品名称以及对应的销售总数</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/def622/#联结表" class="sidebar-link">联结表</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回顾客名称和相关订单号" class="sidebar-link">返回顾客名称和相关订单号</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回顾客名称和相关订单号以及每个订单的总价" class="sidebar-link">返回顾客名称和相关订单号以及每个订单的总价⭐️</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#确定哪些订单购买了-prod-id-为-br01-的产品-二" class="sidebar-link">确定哪些订单购买了 prod_id 为 BR01 的产品（二）</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回购买-prod-id-为-br01-的产品的所有顾客的电子邮件-二" class="sidebar-link">返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#确定最佳顾客的另一种方式-二" class="sidebar-link">确定最佳顾客的另一种方式（二）⭐️</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/def622/#创建高级联结" class="sidebar-link">创建高级联结</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#检索每个顾客的名称和所有的订单号-一" class="sidebar-link">检索每个顾客的名称和所有的订单号（一）</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#检索每个顾客的名称和所有的订单号-二" class="sidebar-link">检索每个顾客的名称和所有的订单号（二）⭐️</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回产品名称和与之相关的订单号" class="sidebar-link">返回产品名称和与之相关的订单号</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#返回产品名称和每一项产品的总订单数" class="sidebar-link">返回产品名称和每一项产品的总订单数</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#列出供应商及其可供产品的数量" class="sidebar-link">列出供应商及其可供产品的数量</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/def622/#组合查询" class="sidebar-link">组合查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/def622/#将两个-select-语句结合起来-一" class="sidebar-link">将两个 SELECT 语句结合起来（一）</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#将两个-select-语句结合起来-二" class="sidebar-link">将两个 SELECT 语句结合起来（二）</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#组合-products-表中的产品名称和-customers-表中的顾客名称" class="sidebar-link">组合 Products 表中的产品名称和 Customers 表中的顾客名称⭐️</a></li><li class="sidebar-sub-header level3"><a href="/pages/def622/#纠错-4" class="sidebar-link">纠错 4</a></li></ul></li></ul></li></ul></section></li></ul></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>Mysql</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>Redis</span> <span class="arrow right"></span></p> <!----></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><span data-v-06225672>数据库</span></li><li data-v-06225672><span data-v-06225672>基础</span></li><li data-v-06225672><span data-v-06225672>SQL</span></li></ul> <div class="info" data-v-06225672><div title="作者" class="author iconfont icon-touxiang" data-v-06225672><a href="javascript:;" data-v-06225672>霜晨月</a></div> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2023-07-24</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">SQL必知必会题解<!----></h1> <!----> <div class="theme-vdoing-content content__default"><h1 id="sql-必知必会题解"><a href="#sql-必知必会题解" class="header-anchor">#</a> SQL 必知必会题解</h1> <blockquote><p>地址：<a href="https://www.nowcoder.com/exam/oj?page=1&amp;tab=SQL%E7%AF%87&amp;topicId=298" target="_blank" rel="noopener noreferrer">牛客网在线编程_SQL篇_SQL必知必会 (nowcoder.com)<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p></blockquote> <h2 id="检索数据"><a href="#检索数据" class="header-anchor">#</a> 检索数据</h2> <h3 id="从-customers-表中检索所有的-id"><a href="#从-customers-表中检索所有的-id" class="header-anchor">#</a> 从 Customers 表中检索所有的 ID</h3> <p>现有表 Customers 如下：</p> <table><thead><tr><th>cust_id</th></tr></thead> <tbody><tr><td>A</td></tr> <tr><td>B</td></tr> <tr><td>C</td></tr></tbody></table> <p>【问题】编写 SQL 语句，从 Customers 表中检索所有的 cust_id</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> cust_id <span class="token keyword">from</span> Customers<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="检索并列出已订购产品的清单"><a href="#检索并列出已订购产品的清单" class="header-anchor">#</a> 检索并列出已订购产品的清单⭐️</h3> <p>表 OrderItems 含有非空的列 prod_id 代表商品 id，包含了所有已订购的商品（有些已被订购多次）。</p> <table><thead><tr><th>prod_id</th></tr></thead> <tbody><tr><td>a1</td></tr> <tr><td>a2</td></tr> <tr><td>a3</td></tr> <tr><td>a4</td></tr> <tr><td>a5</td></tr> <tr><td>a6</td></tr> <tr><td>a7</td></tr></tbody></table> <p>【问题】编写 SQL 语句，检索并列出所有已订购商品（prod_id）的<strong>去重后</strong>的清单。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token keyword">distinct</span> prod_id <span class="token keyword">from</span> OrderItems<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="检索所有列"><a href="#检索所有列" class="header-anchor">#</a> 检索所有列</h3> <p>现在有 Customers 表（表中含有列 cust_id 代表客户 id，cust_name 代表客户姓名）</p> <table><thead><tr><th>cust_id</th> <th>cust_name</th></tr></thead> <tbody><tr><td>a1</td> <td>andy</td></tr> <tr><td>a2</td> <td>ben</td></tr> <tr><td>a3</td> <td>tony</td></tr> <tr><td>a4</td> <td>tom</td></tr> <tr><td>a5</td> <td>an</td></tr> <tr><td>a6</td> <td>lee</td></tr> <tr><td>a7</td> <td>hex</td></tr></tbody></table> <p>【问题】需要编写 SQL 语句，检索所有列。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> cust_id<span class="token punctuation">,</span> cust_name <span class="token keyword">from</span> Customers<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="排序检索数据"><a href="#排序检索数据" class="header-anchor">#</a> 排序检索数据</h2> <h3 id="检索顾客名称并且排序"><a href="#检索顾客名称并且排序" class="header-anchor">#</a> 检索顾客名称并且排序</h3> <p>有表 Customers，cust_id 代表客户 id，cust_name 代表客户姓名。</p> <table><thead><tr><th>cust_id</th> <th>cust_name</th></tr></thead> <tbody><tr><td>a1</td> <td>andy</td></tr> <tr><td>a2</td> <td>ben</td></tr> <tr><td>a3</td> <td>tony</td></tr> <tr><td>a4</td> <td>tom</td></tr> <tr><td>a5</td> <td>an</td></tr> <tr><td>a6</td> <td>lee</td></tr> <tr><td>a7</td> <td>hex</td></tr></tbody></table> <p>【问题】从 Customers 中检索所有的顾客名称（cust_name），并按从 Z 到 A 的顺序显示结果。(其实就是降序)</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> cust_name <span class="token keyword">from</span> Customers <span class="token keyword">order</span> <span class="token keyword">by</span> cust_name <span class="token keyword">desc</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="对顾客-id-和日期排序"><a href="#对顾客-id-和日期排序" class="header-anchor">#</a> 对顾客 ID 和日期排序</h3> <p>有 Orders 表</p> <table><thead><tr><th>cust_id</th> <th>order_num</th> <th>order_date</th></tr></thead> <tbody><tr><td>andy</td> <td>aaaa</td> <td>2021-01-01 00:00:00</td></tr> <tr><td>andy</td> <td>bbbb</td> <td>2021-01-01 12:00:00</td></tr> <tr><td>bob</td> <td>cccc</td> <td>2021-01-10 12:00:00</td></tr> <tr><td>dick</td> <td>dddd</td> <td>2021-01-11 00:00:00</td></tr></tbody></table> <p>【问题】编写 SQL 语句，从 Orders 表中检索顾客 ID（cust_id）和订单号（order_num），并先按顾客 ID 对结果进行排序，再按订单日期倒序排列。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> cust_id<span class="token punctuation">,</span> order_num <span class="token keyword">from</span> Orders <span class="token keyword">order</span> <span class="token keyword">by</span> cust_id<span class="token punctuation">,</span> order_date <span class="token keyword">desc</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="按照数量和价格排序"><a href="#按照数量和价格排序" class="header-anchor">#</a> 按照数量和价格排序⭐️</h3> <p>假设有一个 OrderItems 表</p> <table><thead><tr><th>quantity</th> <th>item_price</th></tr></thead> <tbody><tr><td>1</td> <td>100</td></tr> <tr><td>10</td> <td>1003</td></tr> <tr><td>2</td> <td>500</td></tr></tbody></table> <p>【问题】编写 SQL 语句，显示 OrderItems 表中的数量（quantity）和价格（item_price），并按数量由多到少、价格由高到低排序。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> quantity<span class="token punctuation">,</span> item_price <span class="token keyword">from</span> OrderItems <span class="token keyword">order</span> <span class="token keyword">by</span> quantity <span class="token keyword">desc</span><span class="token punctuation">,</span>item_price <span class="token keyword">desc</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="检查-sql-语句"><a href="#检查-sql-语句" class="header-anchor">#</a> 检查 SQL 语句</h3> <p>有 Vendors 表</p> <table><thead><tr><th>vend_name</th></tr></thead> <tbody><tr><td>海底捞</td></tr> <tr><td>小龙坎</td></tr> <tr><td>大龙燚</td></tr></tbody></table> <p>【问题】下面的 SQL 语句有问题吗？尝试将它改正确，使之能够正确运行，并且返回结果根据 vend_name 逆序排列</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> vend_name<span class="token punctuation">,</span> 
<span class="token keyword">FROM</span> Vendors 
<span class="token keyword">ORDER</span> vend_name <span class="token keyword">DESC</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>答案：少了个 <code>by</code></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> vend_name <span class="token keyword">from</span> Vendors <span class="token keyword">order</span> <span class="token keyword">by</span> vend_name <span class="token keyword">desc</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="过滤数据"><a href="#过滤数据" class="header-anchor">#</a> 过滤数据</h2> <h3 id="返回固定价格的产品"><a href="#返回固定价格的产品" class="header-anchor">#</a> 返回固定价格的产品</h3> <p>有表 Products</p> <table><thead><tr><th>prod_id</th> <th>prod_name</th> <th>prod_price</th></tr></thead> <tbody><tr><td>a0018</td> <td>sockets</td> <td>9.49</td></tr> <tr><td>a0019</td> <td>iphone13</td> <td>600</td></tr> <tr><td>b0018</td> <td>gucci t-shirts</td> <td>1000</td></tr></tbody></table> <p>【问题】从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9.49 美元的产品。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> prod_id<span class="token punctuation">,</span> prod_name <span class="token keyword">from</span> Products <span class="token keyword">where</span> prod_price <span class="token operator">=</span> <span class="token number">9.49</span><span class="token punctuation">;</span> 
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="返回更高价格的产品"><a href="#返回更高价格的产品" class="header-anchor">#</a> 返回更高价格的产品</h3> <p>Products 表</p> <table><thead><tr><th>prod_id</th> <th>prod_name</th> <th>prod_price</th></tr></thead> <tbody><tr><td>a0018</td> <td>sockets</td> <td>9.49</td></tr> <tr><td>a0019</td> <td>iphone13</td> <td>600</td></tr> <tr><td>b0019</td> <td>gucci t-shirts</td> <td>1000</td></tr></tbody></table> <p>【问题】编写 SQL 语句，从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9 美元或更高的产品。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> prod_id<span class="token punctuation">,</span> prod_name <span class="token keyword">from</span> Products <span class="token keyword">where</span> prod_price <span class="token operator">&gt;=</span> <span class="token number">9</span><span class="token punctuation">;</span> 
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="返回产品并且按照价格排序"><a href="#返回产品并且按照价格排序" class="header-anchor">#</a> 返回产品并且按照价格排序⭐️</h3> <p>有 Products 表</p> <table><thead><tr><th>prod_id</th> <th>prod_name</th> <th>prod_price</th></tr></thead> <tbody><tr><td>a0011</td> <td>egg</td> <td>3</td></tr> <tr><td>a0019</td> <td>sockets</td> <td>4</td></tr> <tr><td>b0019</td> <td>coffee</td> <td>15</td></tr></tbody></table> <p>【问题】编写 SQL 语句，返回 Products 表中所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），然后按价格对结果进行排序</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> prod_name<span class="token punctuation">,</span> prod_price
<span class="token keyword">FROM</span> Products
<span class="token keyword">WHERE</span> prod_price <span class="token operator">BETWEEN</span> <span class="token number">3</span> <span class="token operator">AND</span> <span class="token number">6</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> prod_price

<span class="token comment"># 或者</span>
<span class="token keyword">SELECT</span> prod_name<span class="token punctuation">,</span> prod_price
<span class="token keyword">FROM</span> Products
<span class="token keyword">WHERE</span> prod_price <span class="token operator">&gt;=</span> <span class="token number">3</span> <span class="token operator">AND</span> prod_price <span class="token operator">&lt;=</span> <span class="token number">6</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> prod_price
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><h3 id="返回更多的产品"><a href="#返回更多的产品" class="header-anchor">#</a> 返回更多的产品⭐️</h3> <p>OrderItems 表含有：订单号 order_num，quantity 产品数量</p> <table><thead><tr><th>order_num</th> <th>quantity</th></tr></thead> <tbody><tr><td>a1</td> <td>105</td></tr> <tr><td>a2</td> <td>1100</td></tr> <tr><td>a2</td> <td>200</td></tr> <tr><td>a4</td> <td>1121</td></tr> <tr><td>a5</td> <td>10</td></tr> <tr><td>a2</td> <td>19</td></tr> <tr><td>a7</td> <td>5</td></tr></tbody></table> <p>【问题】从 OrderItems 表中检索出所有<strong>不同且不重复</strong>的订单号（order_num），其中每个订单都要包含 100 个或更多的产品。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> order_num
<span class="token keyword">FROM</span> OrderItems
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> order_num
<span class="token keyword">HAVING</span> <span class="token function">SUM</span><span class="token punctuation">(</span>quantity<span class="token punctuation">)</span> <span class="token operator">&gt;=</span> <span class="token number">100</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>知识点：</p> <ul><li><p><code>group by</code>: 字段进行分组可以实现不同且不重复的结果。</p></li> <li><p><code>HAVING</code> 子句：用于过滤分组后的结果，只返回满足条件的分组，这里是筛选满足 <code>SUM(quantity) &gt;= 100</code> 条件的分组。</p></li> <li><p><code>SUM()</code> 函数：用于计算指定字段的总和，这里是计算 quantity 字段的总和。</p></li></ul> <h2 id="高级数据过滤"><a href="#高级数据过滤" class="header-anchor">#</a> 高级数据过滤</h2> <h3 id="检索供应商名称"><a href="#检索供应商名称" class="header-anchor">#</a> 检索供应商名称</h3> <p>Vendors 表有字段供应商名称（vend_name）、供应商国家（vend_country）、供应商州（vend_state）</p> <table><thead><tr><th>vend_name</th> <th>vend_country</th> <th>vend_state</th></tr></thead> <tbody><tr><td>apple</td> <td>USA</td> <td>CA</td></tr> <tr><td>vivo</td> <td>CNA</td> <td>shenzhen</td></tr> <tr><td>huawei</td> <td>CNA</td> <td>xian</td></tr></tbody></table> <p>【问题】编写 SQL 语句，从 Vendors 表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个[CA]）</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> vend_name <span class="token keyword">from</span> Vendors <span class="token keyword">where</span> vend_country <span class="token operator">=</span> <span class="token string">'USA'</span> <span class="token operator">AND</span> vend_state <span class="token operator">=</span> <span class="token string">'CA'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>注意：</p> <ul><li>字符串需要使用 <code>单引号</code> 或 <code>双引号</code> 包裹</li></ul> <h3 id="检索并列出已订购产品的清单-2"><a href="#检索并列出已订购产品的清单-2" class="header-anchor">#</a> 检索并列出已订购产品的清单⭐️</h3> <p>OrderItems 表包含了所有已订购的产品（有些已被订购多次）。</p> <table><thead><tr><th>prod_id</th> <th>order_num</th> <th>quantity</th></tr></thead> <tbody><tr><td>BR01</td> <td>a1</td> <td>105</td></tr> <tr><td>BR02</td> <td>a2</td> <td>1100</td></tr> <tr><td>BR02</td> <td>a2</td> <td>200</td></tr> <tr><td>BR03</td> <td>a4</td> <td>1121</td></tr> <tr><td>BR017</td> <td>a5</td> <td>10</td></tr> <tr><td>BR02</td> <td>a2</td> <td>19</td></tr> <tr><td>BR017</td> <td>a7</td> <td>5</td></tr></tbody></table> <p>【问题】编写 SQL 语句，查找所有订购了数量至少 100 个的 BR01、BR02 或BR03 的订单。你需要返回 OrderItems 表的订单号（order_num）、产品 ID（prod_id）和数量（quantity），并按产品 ID 和数量进行过滤。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> order_num<span class="token punctuation">,</span> prod_id<span class="token punctuation">,</span> quantity 
<span class="token keyword">from</span> OrderItems 
<span class="token keyword">where</span> prod_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token string">'BR01'</span><span class="token punctuation">,</span> <span class="token string">'BR02'</span><span class="token punctuation">,</span><span class="token string">'BR03'</span><span class="token punctuation">)</span> <span class="token operator">and</span> quantity <span class="token operator">&gt;=</span> <span class="token number">100</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="返回所有价格在-3-美元到-6-美元之间的产品的名称和价格"><a href="#返回所有价格在-3-美元到-6-美元之间的产品的名称和价格" class="header-anchor">#</a> 返回所有价格在 3 美元到 6 美元之间的产品的名称和价格</h3> <p>有表 Products</p> <table><thead><tr><th>prod_id</th> <th>prod_name</th> <th>prod_price</th></tr></thead> <tbody><tr><td>a0011</td> <td>egg</td> <td>3</td></tr> <tr><td>a0019</td> <td>sockets</td> <td>4</td></tr> <tr><td>b0019</td> <td>coffee</td> <td>15</td></tr></tbody></table> <p>【问题】编写 SQL 语句，返回所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），使用 AND 操作符，然后按价格对结果进行升序排序</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> prod_name<span class="token punctuation">,</span> prod_price 
<span class="token keyword">from</span> Products 
<span class="token keyword">where</span> prod_price <span class="token operator">between</span> <span class="token number">3</span> <span class="token operator">and</span> <span class="token number">6</span> 
<span class="token keyword">order</span> <span class="token keyword">by</span> prod_price <span class="token keyword">asc</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="纠错-2"><a href="#纠错-2" class="header-anchor">#</a> 纠错 2</h3> <p>供应商表 Vendors 有字段供应商名称 vend_name、供应商国家 vend_country、供应商省份 vend_state</p> <table><thead><tr><th>vend_name</th> <th>vend_country</th> <th>vend_state</th></tr></thead> <tbody><tr><td>apple</td> <td>USA</td> <td>CA</td></tr> <tr><td>vivo</td> <td>CNA</td> <td>shenzhen</td></tr> <tr><td>huawei</td> <td>CNA</td> <td>xian</td></tr></tbody></table> <p>【问题】修改正确下面 sql，使之正确返回</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> vend_name 
<span class="token keyword">FROM</span> Vendors 
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> vend_name
<span class="token keyword">WHERE</span> vend_country <span class="token operator">=</span> <span class="token string">'USA'</span> <span class="token operator">AND</span> vend_state <span class="token operator">=</span> <span class="token string">'CA'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> vend_name 
<span class="token keyword">FROM</span> Vendors 
<span class="token keyword">WHERE</span> vend_country <span class="token operator">=</span> <span class="token string">'USA'</span> <span class="token operator">AND</span> vend_state <span class="token operator">=</span> <span class="token string">'CA'</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> vend_name 
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>知识点：考察 sql 关键词的顺序。</p> <p><code>order by</code> 的位置位于所有语句的倒数第二，倒数第一是 <code>limit</code></p> <h2 id="用通配符进行过滤"><a href="#用通配符进行过滤" class="header-anchor">#</a> 用通配符进行过滤</h2> <p>SQL 通配符必须与 <code>LIKE</code> 运算符一起使用</p> <table><thead><tr><th>通配符</th> <th>说明</th></tr></thead> <tbody><tr><td>%</td> <td>用于表示零个或多个字符，可以匹配任意长度的字符串。例如，'abc%' 可以匹配 'abc'、'abcd'、'abcxyz' 等。</td></tr> <tr><td>_</td> <td>用于表示单个字符，可以匹配任意单个字符。例如，'a_c' 可以匹配 'abc'、'adc'、'axc' 等，但不能匹配 'abcd'、'abcde' 等。</td></tr> <tr><td>[ ]</td> <td>用于表示<strong>字符范围</strong>，可以匹配指定范围内的任意一个字符。例如，'[a-z]' 可以匹配任意小写字母。</td></tr> <tr><td>[^ ]</td> <td>用于表示字符集的补集，可以匹配不在指定字符集中的任意一个字符。例如，<code>'[^aeiou]'</code> 可以匹配任意非元音字母。</td></tr></tbody></table> <ul><li><code>[^abc]</code>：表示匹配除了 a、b、c 以外的任意一个字符。</li> <li><code>[^a-z]</code>：表示匹配除了小写字母a到z以外的任意一个字符。</li> <li><code>[^0-9]</code>：表示匹配除了数字0到9以外的任意一个字符。</li> <li><code>[^aeiou]</code>：表示匹配除了元音字母 a、e、i、o、u 以外的任意一个字母。</li></ul> <h3 id="检索产品名称和描述-一"><a href="#检索产品名称和描述-一" class="header-anchor">#</a> 检索产品名称和描述（一）⭐️</h3> <p>Products 表</p> <table><thead><tr><th>prod_name</th> <th>prod_desc</th></tr></thead> <tbody><tr><td>a0011</td> <td>usb</td></tr> <tr><td>a0019</td> <td>iphone13</td></tr> <tr><td>b0019</td> <td>gucci t-shirts</td></tr> <tr><td>c0019</td> <td>gucci toy</td></tr> <tr><td>d0019</td> <td>lego toy</td></tr></tbody></table> <p>【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品名称</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> prod_name<span class="token punctuation">,</span> prod_desc 
<span class="token keyword">from</span> Products 
<span class="token keyword">where</span> prod_desc <span class="token operator">like</span> <span class="token string">'%toy%'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="检索产品名称和描述-二"><a href="#检索产品名称和描述-二" class="header-anchor">#</a> 检索产品名称和描述（二）⭐️</h3> <p>Products 表</p> <table><thead><tr><th>prod_name</th> <th>prod_desc</th></tr></thead> <tbody><tr><td>a0011</td> <td>usb</td></tr> <tr><td>a0019</td> <td>iphone13</td></tr> <tr><td>b0019</td> <td>gucci t-shirts</td></tr> <tr><td>c0019</td> <td>gucci toy</td></tr> <tr><td>d0019</td> <td>lego toy</td></tr></tbody></table> <p>【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品，最后按【产品名称】对结果进行排序（<strong>默认升序</strong>）。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> prod_name<span class="token punctuation">,</span> prod_desc 
<span class="token keyword">from</span> Products 
<span class="token keyword">where</span> prod_desc <span class="token operator">not</span> <span class="token operator">like</span> <span class="token string">'%toy%'</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> prod_name
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>知识点：</p> <ul><li><code>not like</code> 不包含</li></ul> <h3 id="检索产品名称和描述-三"><a href="#检索产品名称和描述-三" class="header-anchor">#</a> 检索产品名称和描述（三）⭐️</h3> <p>Products 表</p> <table><thead><tr><th>prod_name</th> <th>prod_desc</th></tr></thead> <tbody><tr><td>a0011</td> <td>usb</td></tr> <tr><td>a0019</td> <td>iphone13</td></tr> <tr><td>b0019</td> <td>gucci t-shirts</td></tr> <tr><td>c0019</td> <td>gucci toy</td></tr> <tr><td>d0019</td> <td>lego carrots toy</td></tr></tbody></table> <p>【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 toy 和 carrots 的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个 LIKE 比较。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> prod_name<span class="token punctuation">,</span> prod_desc 
<span class="token keyword">from</span> Products 
<span class="token comment"># where prod_desc like '%carrots%toy%'</span>
<span class="token keyword">where</span> prod_desc <span class="token operator">like</span> <span class="token string">'%toy%'</span> <span class="token operator">and</span> prod_desc <span class="token operator">like</span> <span class="token string">'%carrots%'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>注意：<code>and</code> 的两边都要写上字段名</p> <h3 id="检索产品名称和描述-四"><a href="#检索产品名称和描述-四" class="header-anchor">#</a> 检索产品名称和描述（四）</h3> <p>Products 表</p> <table><thead><tr><th>prod_name</th> <th>prod_desc</th></tr></thead> <tbody><tr><td>a0011</td> <td>usb</td></tr> <tr><td>a0019</td> <td>iphone13</td></tr> <tr><td>b0019</td> <td>gucci t-shirts</td></tr> <tr><td>c0019</td> <td>gucci toy</td></tr> <tr><td>d0019</td> <td>lego toy carrots</td></tr></tbody></table> <p>【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以<strong>先后顺序</strong>同时出现 toy 和 carrots 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> prod_name<span class="token punctuation">,</span> prod_desc 
<span class="token keyword">from</span> Products 
<span class="token keyword">where</span> prod_desc <span class="token operator">like</span> <span class="token string">'%toy%carrots%'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h2 id="创建计算字段"><a href="#创建计算字段" class="header-anchor">#</a> 创建计算字段</h2> <h3 id="别名"><a href="#别名" class="header-anchor">#</a> 别名</h3> <p>别名的常见用法是在检索出的结果中重命名表的列字段（为了符合特定的报表要求或客户需求）。有表 Vendors 代表供应商信息，vend_id 供应商 id、vend_name 供应商名称、vend_address 供应商地址、vend_city 供应商城市。</p> <table><thead><tr><th>vend_id</th> <th>vend_name</th> <th>vend_address</th> <th>vend_city</th></tr></thead> <tbody><tr><td>a001</td> <td>tencent cloud</td> <td>address1</td> <td>shenzhen</td></tr> <tr><td>a002</td> <td>huawei cloud</td> <td>address2</td> <td>dongguan</td></tr> <tr><td>a003</td> <td>aliyun cloud</td> <td>address3</td> <td>hangzhou</td></tr> <tr><td>a003</td> <td>netease cloud</td> <td>address4</td> <td>guangzhou</td></tr></tbody></table> <p>【问题】编写 SQL 语句，从 Vendors 表中检索 vend_id、vend_name、vend_address 和 vend_city，将 vend_name 重命名为 vname，将 vend_city 重命名为 vcity，将 vend_address 重命名为 vaddress，按供应商名称对结果进行升序排序。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> vend_id<span class="token punctuation">,</span> vend_name <span class="token keyword">as</span> vname<span class="token punctuation">,</span> vend_address <span class="token keyword">as</span> vaddress<span class="token punctuation">,</span> vend_city <span class="token keyword">as</span> vcity
<span class="token keyword">from</span> Vendors
<span class="token keyword">order</span> <span class="token keyword">by</span> vname

<span class="token comment"># as 可以省略</span>
<span class="token keyword">SELECT</span> vend_id<span class="token punctuation">,</span> vend_name vname<span class="token punctuation">,</span> vend_address vaddress<span class="token punctuation">,</span> vend_city vcity
<span class="token keyword">FROM</span> Vendors
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> vname
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><h3 id="打折"><a href="#打折" class="header-anchor">#</a> 打折⭐️</h3> <p>我们的示例商店正在进行打折促销，所有产品均降价 10%。Products 表包含 prod_id 产品 id、prod_price 产品价格</p> <p>【问题】编写 SQL 语句，从 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一个包含促销价格的计算字段。提示：可以乘以 0.9，得到原价的 90%（即 10% 的折扣）</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> prod_id<span class="token punctuation">,</span> prod_price<span class="token punctuation">,</span> prod_price <span class="token operator">*</span> <span class="token number">0.9</span> <span class="token keyword">as</span> sale_price
<span class="token keyword">from</span> Products
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h2 id="使用函数处理数据"><a href="#使用函数处理数据" class="header-anchor">#</a> 使用函数处理数据</h2> <h3 id="顾客登录名"><a href="#顾客登录名" class="header-anchor">#</a> 顾客登录名⭐️</h3> <p>我们的商店已经上线了，正在创建顾客账户。所有用户都需要登录名，默认登录名是其名称和所在城市的组合。</p> <p>给出 Customers 表如下：</p> <table><thead><tr><th>cust_id</th> <th>cust_name</th> <th>cust_contact</th> <th>cust_city</th></tr></thead> <tbody><tr><td>a1</td> <td>Andy Li</td> <td>Andy Li</td> <td>Oak Park</td></tr> <tr><td>a2</td> <td>Ben Liu</td> <td>Ben Liu</td> <td>Oak Park</td></tr> <tr><td>a3</td> <td>Tony Dai</td> <td>Tony Dai</td> <td>Oak Park</td></tr> <tr><td>a4</td> <td>Tom Chen</td> <td>Tom Chen</td> <td>Oak Park</td></tr> <tr><td>a5</td> <td>An Li</td> <td>An Li</td> <td>Oak Park</td></tr> <tr><td>a6</td> <td>Lee Chen</td> <td>Lee Chen</td> <td>Oak Park</td></tr> <tr><td>a7</td> <td>Hex Liu</td> <td>Hex Liu</td> <td>Oak Park</td></tr></tbody></table> <p>【问题】编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> cust_id<span class="token punctuation">,</span> cust_name<span class="token punctuation">,</span> upper<span class="token punctuation">(</span>concat<span class="token punctuation">(</span>substring<span class="token punctuation">(</span>cust_contact<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> substring<span class="token punctuation">(</span>cust_city<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">as</span> user_login
<span class="token keyword">from</span> Customers
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>知识点：</p> <p>关键词：substing,concat,upper</p> <p>用法：</p> <ul><li>字符串的截取：substring(字符串，起始位置，截取字符数）</li> <li>字符串的拼接：concat(字符串1，字符串2，字符串3,...)</li> <li>字母大写：upper(字符串）</li></ul> <h3 id="返回-2020-年-1-月的所有订单的订单号和订单日期"><a href="#返回-2020-年-1-月的所有订单的订单号和订单日期" class="header-anchor">#</a> 返回 2020 年 1 月的所有订单的订单号和订单日期⭐️</h3> <p>Orders 订单表</p> <table><thead><tr><th>order_num</th> <th>order_date</th></tr></thead> <tbody><tr><td>a0001</td> <td>2020-01-01 00:00:00</td></tr> <tr><td>a0002</td> <td>2020-01-02 00:00:00</td></tr> <tr><td>a0003</td> <td>2020-01-01 12:00:00</td></tr> <tr><td>a0004</td> <td>2020-02-01 00:00:00</td></tr> <tr><td>a0005</td> <td>2020-03-01 00:00:00</td></tr></tbody></table> <p>【问题】编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期升序排序</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> order_num<span class="token punctuation">,</span> order_date
<span class="token keyword">from</span> Orders
<span class="token keyword">where</span> <span class="token keyword">month</span><span class="token punctuation">(</span>order_date<span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token string">'01'</span> <span class="token operator">and</span> <span class="token keyword">year</span><span class="token punctuation">(</span>order_date<span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token string">'2020'</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> order_date
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>知识点：</p> <p><a href="https://cmty256.github.io/pages/ed0fa0/#%E6%97%A5%E6%9C%9F%E5%92%8C%E6%97%B6%E9%97%B4%E5%A4%84%E7%90%86" target="_blank" rel="noopener noreferrer">SQL语法基础小结 | 日期和时间处理<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p> <blockquote><p>其他解法</p></blockquote> <p><em>字符串匹配</em>（近似查找法）</p> <p>用 like 来查找</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> order_num<span class="token punctuation">,</span> order_date 
<span class="token keyword">from</span> Orders
<span class="token keyword">where</span> order_date <span class="token operator">like</span> <span class="token string">'2020-01%'</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> order_date
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>切割字符串</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> order_num<span class="token punctuation">,</span> order_date 
<span class="token keyword">from</span> Orders
<span class="token keyword">where</span> <span class="token keyword">left</span><span class="token punctuation">(</span>order_date<span class="token punctuation">,</span> <span class="token number">7</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token string">'2020-01'</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> order_date
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>字符串比较</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span>
<span class="token keyword">from</span> Orders
<span class="token keyword">where</span> order_date <span class="token operator">&gt;=</span> <span class="token string">'2020-01-01 00:00:00'</span> <span class="token operator">and</span> order_date <span class="token operator">&lt;=</span> <span class="token string">'2020-01-31 23:59:59'</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> order_date<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>用正则来查找（效率不如 like，能用 like 就用 like）</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> order_num<span class="token punctuation">,</span> order_date 
<span class="token keyword">from</span> Orders
<span class="token keyword">where</span> order_date <span class="token operator">regexp</span> <span class="token string">'2020-01'</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> order_date
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><em>时间函数匹配</em></p> <p>利用<a href="https://www.w3school.com.cn/sql/func_date_format.asp" target="_blank" rel="noopener noreferrer">date_format<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>函数 （参考其中的匹配规则进行匹配）</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> order_num<span class="token punctuation">,</span> order_date 
<span class="token keyword">from</span> Orders
<span class="token keyword">where</span> date_format<span class="token punctuation">(</span>order_date<span class="token punctuation">,</span> <span class="token string">'%Y-%m'</span><span class="token punctuation">)</span><span class="token operator">=</span><span class="token string">'2020-01'</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> order_date
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h2 id="汇总数据"><a href="#汇总数据" class="header-anchor">#</a> 汇总数据</h2> <h3 id="确定已售出产品的总数"><a href="#确定已售出产品的总数" class="header-anchor">#</a> 确定已售出产品的总数</h3> <p>OrderItems 表代表售出的产品，quantity 代表售出商品数量。</p> <table><thead><tr><th>quantity</th></tr></thead> <tbody><tr><td>10</td></tr> <tr><td>100</td></tr> <tr><td>1000</td></tr> <tr><td>10001</td></tr> <tr><td>2</td></tr> <tr><td>15</td></tr></tbody></table> <p>【问题】编写 SQL 语句，确定已售出产品的总数。返回 <code>items_ordered</code> 列名，表示已售出商品的总数。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token function">sum</span><span class="token punctuation">(</span>quantity<span class="token punctuation">)</span> <span class="token keyword">as</span> items_ordered
<span class="token keyword">from</span> OrderItems
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="确定已售出产品项-br01-的总数"><a href="#确定已售出产品项-br01-的总数" class="header-anchor">#</a> 确定已售出产品项 BR01 的总数</h3> <p>OrderItems 表代表售出的产品，quantity 代表售出商品数量，产品项为 prod_item。</p> <table><thead><tr><th>quantity</th> <th>prod_id</th></tr></thead> <tbody><tr><td>10</td> <td>AR01</td></tr> <tr><td>100</td> <td>AR10</td></tr> <tr><td>1000</td> <td>BR01</td></tr> <tr><td>10001</td> <td>BR010</td></tr></tbody></table> <p>【问题】修改创建的语句，确定已售出产品项（prod_item）为 &quot;BR01&quot; 的总数。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token function">sum</span><span class="token punctuation">(</span>quantity<span class="token punctuation">)</span> <span class="token keyword">as</span> items_ordered
<span class="token keyword">from</span> OrderItems
<span class="token keyword">where</span> prod_id <span class="token operator">=</span> <span class="token string">'BR01'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="确定-products-表中价格不超过-10-美元的最贵产品的价格"><a href="#确定-products-表中价格不超过-10-美元的最贵产品的价格" class="header-anchor">#</a> 确定 Products 表中价格不超过 10 美元的最贵产品的价格</h3> <p>Products 表</p> <table><thead><tr><th>prod_price</th></tr></thead> <tbody><tr><td>9.49</td></tr> <tr><td>600</td></tr> <tr><td>1000</td></tr></tbody></table> <p>【问题】编写 SQL 语句，确定 Products 表中价格不超过 10 美元的最贵产品的价格（prod_price）。将计算所得的字段命名为 max_price。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token function">max</span><span class="token punctuation">(</span>prod_price<span class="token punctuation">)</span> <span class="token keyword">as</span> max_price
<span class="token keyword">from</span> Products
<span class="token keyword">where</span> prod_price <span class="token operator">&lt;=</span> <span class="token number">10</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h2 id="分组数据"><a href="#分组数据" class="header-anchor">#</a> 分组数据</h2> <p><code>GROUP BY</code>：</p> <ul><li><code>GROUP BY</code> 子句将记录分组到汇总行中。</li> <li><code>GROUP BY</code> 为每个组返回一个记录。</li> <li><code>GROUP BY</code> 通常还涉及聚合<code>COUNT</code>，<code>MAX</code>，<code>SUM</code>，<code>AVG</code> 等。</li> <li><code>GROUP BY</code> 可以按一列或多列进行分组。</li> <li><code>GROUP BY</code> 按分组字段进行排序后，<code>ORDER BY</code> 可以以汇总字段来进行排序。</li></ul> <p><code>HAVING</code>：</p> <ul><li><code>HAVING</code> 用于对汇总的 <code>GROUP BY</code> 结果进行过滤。</li> <li><code>HAVING</code> 必须要与 <code>GROUP BY</code> 连用。</li> <li><code>WHERE</code> 和 <code>HAVING</code> 可以在相同的查询中。</li></ul> <p><code>HAVING</code> vs <code>WHERE</code>：</p> <ul><li><code>WHERE</code>：过滤指定的行，后面不能加聚合函数（分组函数）。</li> <li><code>HAVING</code>：过滤分组，必须要与 <code>GROUP BY</code> 连用，不能单独使用。</li></ul> <h3 id="返回每个订单号各有多少行数"><a href="#返回每个订单号各有多少行数" class="header-anchor">#</a> 返回每个订单号各有多少行数</h3> <p>OrderItems 表包含每个订单的每个产品</p> <table><thead><tr><th>order_num</th></tr></thead> <tbody><tr><td>a002</td></tr> <tr><td>a002</td></tr> <tr><td>a002</td></tr> <tr><td>a004</td></tr> <tr><td>a007</td></tr></tbody></table> <p>【问题】编写 SQL 语句，返回每个订单号（order_num）各有多少行数（order_lines），并按 order_lines 对结果进行升序排序。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> order_num<span class="token punctuation">,</span> <span class="token function">count</span><span class="token punctuation">(</span>order_num<span class="token punctuation">)</span> <span class="token keyword">as</span> order_lines
<span class="token keyword">from</span> OrderItems
<span class="token keyword">group</span> <span class="token keyword">by</span> order_num
<span class="token keyword">order</span> <span class="token keyword">by</span> order_lines
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><code>count(*)</code>,<code>count(列名)</code> 都可以，区别在于：<code>count(列名)</code> 是统计非 NULL 的行数；</p> <h3 id="每个供应商成本最低的产品"><a href="#每个供应商成本最低的产品" class="header-anchor">#</a> 每个供应商成本最低的产品</h3> <p>有 Products 表，含有字段 prod_price 代表产品价格，vend_id 代表供应商 id</p> <table><thead><tr><th>vend_id</th> <th>prod_price</th></tr></thead> <tbody><tr><td>a0011</td> <td>100</td></tr> <tr><td>a0019</td> <td>0.1</td></tr> <tr><td>b0019</td> <td>1000</td></tr> <tr><td>b0019</td> <td>6980</td></tr> <tr><td>b0019</td> <td>20</td></tr></tbody></table> <p>【问题】编写 SQL 语句，返回名为 cheapest_item 的字段，该字段包含每个供应商成本最低的产品（使用 Products 表中的 prod_price），然后从最低成本到最高成本对结果进行升序排序。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> vend_id<span class="token punctuation">,</span> <span class="token function">min</span><span class="token punctuation">(</span>prod_price<span class="token punctuation">)</span> <span class="token keyword">as</span> cheapest_item 
<span class="token keyword">from</span> Products
<span class="token keyword">group</span> <span class="token keyword">by</span> vend_id
<span class="token keyword">order</span> <span class="token keyword">by</span> cheapest_item
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="返回订单数量总和不小于-100-的所有订单的订单号"><a href="#返回订单数量总和不小于-100-的所有订单的订单号" class="header-anchor">#</a> 返回订单数量总和不小于 100 的所有订单的订单号</h3> <p>OrderItems 代表订单商品表，包括：订单号 order_num 和订单数量 quantity。</p> <table><thead><tr><th>order_num</th> <th>quantity</th></tr></thead> <tbody><tr><td>a1</td> <td>105</td></tr> <tr><td>a2</td> <td>1100</td></tr> <tr><td>a2</td> <td>200</td></tr> <tr><td>a4</td> <td>1121</td></tr> <tr><td>a5</td> <td>10</td></tr> <tr><td>a2</td> <td>19</td></tr> <tr><td>a7</td> <td>5</td></tr></tbody></table> <p>【问题】请编写 SQL 语句，返回订单数量总和不小于 100 的所有订单号，最后结果按照订单号升序排序。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> order_num
<span class="token keyword">from</span> OrderItems
<span class="token keyword">group</span> <span class="token keyword">by</span> order_num
<span class="token keyword">having</span> <span class="token function">sum</span><span class="token punctuation">(</span>quantity<span class="token punctuation">)</span> <span class="token operator">&gt;=</span> <span class="token number">100</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> order_num
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="计算总和"><a href="#计算总和" class="header-anchor">#</a> 计算总和</h3> <p>OrderItems 表代表订单信息，包括字段：订单号 order_num 和 item_price 商品售出价格、 quantity 商品数量。</p> <table><thead><tr><th>order_num</th> <th>item_price</th> <th>quantity</th></tr></thead> <tbody><tr><td>a1</td> <td>10</td> <td>105</td></tr> <tr><td>a2</td> <td>1</td> <td>1100</td></tr> <tr><td>a2</td> <td>1</td> <td>200</td></tr> <tr><td>a4</td> <td>2</td> <td>1121</td></tr> <tr><td>a5</td> <td>5</td> <td>10</td></tr> <tr><td>a2</td> <td>1</td> <td>19</td></tr> <tr><td>a7</td> <td>7</td> <td>5</td></tr></tbody></table> <p>【问题】编写 SQL 语句，根据订单号聚合，返回订单总价不小于 1000 的所有订单号，最后的结果按订单号进行升序排序。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> order_num<span class="token punctuation">,</span> <span class="token function">sum</span><span class="token punctuation">(</span>item_price <span class="token operator">*</span> quantity<span class="token punctuation">)</span> <span class="token keyword">as</span> total_priceice
<span class="token keyword">from</span> OrderItems
<span class="token keyword">group</span> <span class="token keyword">by</span> order_num
<span class="token keyword">having</span> <span class="token function">sum</span><span class="token punctuation">(</span>item_price <span class="token operator">*</span> quantity<span class="token punctuation">)</span> <span class="token operator">&gt;=</span> <span class="token number">1000</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> order_num
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="纠错-3"><a href="#纠错-3" class="header-anchor">#</a> 纠错 3⭐️</h3> <p>OrderItems 表含有 order_num 订单号</p> <table><thead><tr><th>order_num</th></tr></thead> <tbody><tr><td>a002</td></tr> <tr><td>a002</td></tr> <tr><td>a002</td></tr> <tr><td>a004</td></tr> <tr><td>a007</td></tr></tbody></table> <p>【问题】将下面代码修改正确后执行</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> order_num<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> items 
<span class="token keyword">FROM</span> OrderItems 
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> items 
<span class="token keyword">HAVING</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token operator">&gt;=</span> <span class="token number">3</span> 
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> items<span class="token punctuation">,</span> order_num<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> order_num<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> items 
<span class="token keyword">FROM</span> OrderItems 
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> order_num 
<span class="token comment"># HAVING COUNT(*) &gt;= 3 这个也可</span>
<span class="token keyword">HAVING</span> items <span class="token operator">&gt;=</span> <span class="token number">3</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> items<span class="token punctuation">,</span> order_num<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p><em>知识点：执行顺序问题，别名的使用规则</em></p> <ol><li>SQL 查询的执行顺序是先执行 <code>FROM</code>、<code>WHERE</code>、<code>GROUP BY</code>、<code>HAVING</code> 和最后是 <code>SELECT</code>。</li></ol> <p><code>SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING items &gt;= 3 ORDER BY items, order_num;</code> 这个使用别名不报错；</p> <p><code>select order_num, sum(item_price * quantity) as total_price from OrderItems group by order_num having total_price &gt;= 1000 order by order_num</code> 但为什么这个使用别名就会报错</p> <ol start="2"><li>在 SQL 查询中，使用别名通常分为两种情况：</li></ol> <ul><li>使用别名在 <code>SELECT</code> 子句中展示<strong>计算结果</strong>：这种情况下，别名可以直接在其他子句中使用，如在 <code>GROUP BY</code>、<code>HAVING</code>、<code>ORDER BY</code> 等子句中使用别名进行条件过滤或排序。这就是为什么你的第一个查询中使用别名 <code>items</code> 在 <code>HAVING</code> 子句中不会报错的原因。</li> <li>使用别名在 <code>HAVING</code> 子句中进行<strong>条件过滤</strong>：这种情况下，数据库管理系统不会识别别名，因为 <code>HAVING</code> 子句是在 <code>GROUP BY</code> 子句之后执行的，此时数据库还没有计算别名对应的结果。所以在 <code>HAVING</code> 子句中使用别名进行条件过滤是会报错的。</li></ul> <h2 id="使用子查询"><a href="#使用子查询" class="header-anchor">#</a> 使用子查询</h2> <h3 id="返回购买价格为-10-美元或以上产品的顾客列表"><a href="#返回购买价格为-10-美元或以上产品的顾客列表" class="header-anchor">#</a> 返回购买价格为 10 美元或以上产品的顾客列表</h3> <p>OrderItems 表示订单商品表，含有字段订单号：order_num、订单价格：item_price；Orders表代表订单信息表，含有顾客id：cust_id 和订单号：order_num</p> <p>OrderItems 表</p> <table><thead><tr><th>order_num</th> <th>item_price</th></tr></thead> <tbody><tr><td>a1</td> <td>10</td></tr> <tr><td>a2</td> <td>1</td></tr> <tr><td>a2</td> <td>1</td></tr> <tr><td>a4</td> <td>2</td></tr> <tr><td>a5</td> <td>5</td></tr> <tr><td>a2</td> <td>1</td></tr> <tr><td>a7</td> <td>7</td></tr></tbody></table> <p>Orders 表</p> <table><thead><tr><th>order_num</th> <th>cust_id</th></tr></thead> <tbody><tr><td>a1</td> <td>cust10</td></tr> <tr><td>a2</td> <td>cust1</td></tr> <tr><td>a2</td> <td>cust1</td></tr> <tr><td>a4</td> <td>cust2</td></tr> <tr><td>a5</td> <td>cust5</td></tr> <tr><td>a2</td> <td>cust1</td></tr> <tr><td>a7</td> <td>cust7</td></tr></tbody></table> <p>【问题】使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。
注意：你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用 Order 表检索这些匹配订单的顾客 ID（cust_id）。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> cust_id
<span class="token keyword">from</span> Orders
<span class="token keyword">where</span> order_num <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> order_num
                   <span class="token keyword">from</span> OrderItems
                   <span class="token keyword">group</span> <span class="token keyword">by</span> order_num
                   <span class="token keyword">having</span> <span class="token function">sum</span><span class="token punctuation">(</span>item_price<span class="token punctuation">)</span> <span class="token operator">&gt;=</span> <span class="token number">10</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><h3 id="确定哪些订单购买了-prod-id-为-br01-的产品-一"><a href="#确定哪些订单购买了-prod-id-为-br01-的产品-一" class="header-anchor">#</a> 确定哪些订单购买了 prod_id 为 BR01 的产品（一）</h3> <p>表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date</p> <p>OrderItems 表</p> <table><thead><tr><th>prod_id</th> <th>order_num</th></tr></thead> <tbody><tr><td>BR01</td> <td>a0001</td></tr> <tr><td>BR01</td> <td>a0002</td></tr> <tr><td>BR02</td> <td>a0003</td></tr> <tr><td>BR02</td> <td>a0013</td></tr></tbody></table> <p>Orders 表</p> <table><thead><tr><th>order_num</th> <th>cust_id</th> <th>order_date</th></tr></thead> <tbody><tr><td>a0001</td> <td>cust10</td> <td>2022-01-01 00:00:00</td></tr> <tr><td>a0002</td> <td>cust1</td> <td>2022-01-01 00:01:00</td></tr> <tr><td>a0003</td> <td>cust1</td> <td>2022-01-02 00:00:00</td></tr> <tr><td>a0013</td> <td>cust2</td> <td>2022-01-01 00:20:00</td></tr></tbody></table> <p>【问题】</p> <p>编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 &quot;BR01&quot; 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> cust_id<span class="token punctuation">,</span> order_date
<span class="token keyword">from</span> Orders
<span class="token keyword">where</span> order_num <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> order_num
                   <span class="token keyword">from</span> OrderItems
                   <span class="token keyword">where</span> prod_id <span class="token operator">=</span> <span class="token string">'BR01'</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="返回购买-prod-id-为-br01-的产品的所有顾客的电子邮件-一"><a href="#返回购买-prod-id-为-br01-的产品的所有顾客的电子邮件-一" class="header-anchor">#</a> 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）</h3> <p>你想知道订购 BR01 产品的日期，有表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date；Customers 表含有 cust_email 顾客邮件和 cust_id 顾客 id</p> <p>OrderItems 表</p> <table><thead><tr><th>prod_id</th> <th>order_num</th></tr></thead> <tbody><tr><td>BR01</td> <td>a0001</td></tr> <tr><td>BR01</td> <td>a0002</td></tr> <tr><td>BR02</td> <td>a0003</td></tr> <tr><td>BR02</td> <td>a0013</td></tr></tbody></table> <p>Orders 表</p> <table><thead><tr><th>order_num</th> <th>cust_id</th> <th>order_date</th></tr></thead> <tbody><tr><td>a0001</td> <td>cust10</td> <td>2022-01-01 00:00:00</td></tr> <tr><td>a0002</td> <td>cust1</td> <td>2022-01-01 00:01:00</td></tr> <tr><td>a0003</td> <td>cust1</td> <td>2022-01-02 00:00:00</td></tr> <tr><td>a0013</td> <td>cust2</td> <td>2022-01-01 00:20:00</td></tr></tbody></table> <p>Customers 表代表顾客信息，cust_id 为顾客 id，cust_email 为顾客 email</p> <p>【问题】返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。</p> <p>提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment"># 写法 1：子查询</span>
<span class="token keyword">select</span> cust_email
<span class="token keyword">from</span> Customers 
<span class="token keyword">where</span> cust_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> cust_id
                   <span class="token keyword">from</span> Orders
                   <span class="token keyword">where</span> order_num <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> order_num
                                       <span class="token keyword">from</span> OrderItems
                                       <span class="token keyword">where</span> prod_id <span class="token operator">=</span> <span class="token string">'BR01'</span><span class="token punctuation">)</span><span class="token punctuation">)</span>                                                                          
<span class="token comment"># 写法 2: 连接表（inner join）</span>
<span class="token keyword">SELECT</span> c<span class="token punctuation">.</span>cust_email
<span class="token keyword">FROM</span> OrderItems a<span class="token punctuation">,</span>Orders b<span class="token punctuation">,</span>Customers c
<span class="token keyword">WHERE</span> a<span class="token punctuation">.</span>order_num <span class="token operator">=</span> b<span class="token punctuation">.</span>order_num <span class="token operator">AND</span> b<span class="token punctuation">.</span>cust_id <span class="token operator">=</span> c<span class="token punctuation">.</span>cust_id <span class="token operator">AND</span> a<span class="token punctuation">.</span>prod_id <span class="token operator">=</span> <span class="token string">'BR01'</span>

<span class="token comment"># 写法 3：连接表（left join）</span>
<span class="token keyword">SELECT</span> c<span class="token punctuation">.</span>cust_email
<span class="token keyword">FROM</span> Orders a <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span>
  OrderItems b <span class="token keyword">ON</span> a<span class="token punctuation">.</span>order_num <span class="token operator">=</span> b<span class="token punctuation">.</span>order_num <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span>
  Customers c <span class="token keyword">ON</span> a<span class="token punctuation">.</span>cust_id <span class="token operator">=</span> c<span class="token punctuation">.</span>cust_id
<span class="token keyword">WHERE</span> b<span class="token punctuation">.</span>prod_id <span class="token operator">=</span> <span class="token string">'BR01'</span>                             
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br></div></div><h3 id="返回每个顾客不同订单的总金额"><a href="#返回每个顾客不同订单的总金额" class="header-anchor">#</a> 返回每个顾客不同订单的总金额⭐️</h3> <p>我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。</p> <p>OrderItems 表代表订单信息，OrderItems 表有订单号：order_num 和商品售出价格：item_price、商品数量：quantity。</p> <table><thead><tr><th>order_num</th> <th>item_price</th> <th>quantity</th></tr></thead> <tbody><tr><td>a0001</td> <td>10</td> <td>105</td></tr> <tr><td>a0002</td> <td>1</td> <td>1100</td></tr> <tr><td>a0002</td> <td>1</td> <td>200</td></tr> <tr><td>a0013</td> <td>2</td> <td>1121</td></tr> <tr><td>a0003</td> <td>5</td> <td>10</td></tr> <tr><td>a0003</td> <td>1</td> <td>19</td></tr> <tr><td>a0003</td> <td>7</td> <td>5</td></tr></tbody></table> <p>Orders 表订单号：order_num、顾客 id：cust_id</p> <table><thead><tr><th>order_num</th> <th>cust_id</th></tr></thead> <tbody><tr><td>a0001</td> <td>cust10</td></tr> <tr><td>a0002</td> <td>cust1</td></tr> <tr><td>a0003</td> <td>cust1</td></tr> <tr><td>a0013</td> <td>cust2</td></tr></tbody></table> <p>【问题】</p> <p>编写 SQL 语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回 total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。</p> <p>提示：你之前已经使用 SUM() 计算订单总数。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment"># 写法 1：子查询</span>
<span class="token keyword">select</span> o<span class="token punctuation">.</span>cust_id <span class="token keyword">as</span> cust_id<span class="token punctuation">,</span> tb<span class="token punctuation">.</span>total_ordered <span class="token keyword">as</span> total_ordered
<span class="token keyword">from</span> <span class="token punctuation">(</span><span class="token keyword">select</span> order_num<span class="token punctuation">,</span> <span class="token function">sum</span><span class="token punctuation">(</span>item_price <span class="token operator">*</span> quantity<span class="token punctuation">)</span> <span class="token keyword">as</span> total_ordered
    <span class="token keyword">from</span> OrderItems 
    <span class="token keyword">group</span> <span class="token keyword">by</span> order_num<span class="token punctuation">)</span> <span class="token keyword">as</span> tb<span class="token punctuation">,</span>
  Orders  o
<span class="token keyword">where</span> tb<span class="token punctuation">.</span>order_num <span class="token operator">=</span> o<span class="token punctuation">.</span>order_num
<span class="token keyword">order</span> <span class="token keyword">by</span> total_ordered <span class="token keyword">desc</span>

<span class="token comment"># 写法 2：连接表</span>
<span class="token keyword">select</span> b<span class="token punctuation">.</span>cust_id<span class="token punctuation">,</span> <span class="token function">sum</span><span class="token punctuation">(</span>a<span class="token punctuation">.</span>quantity <span class="token operator">*</span> a<span class="token punctuation">.</span>item_price<span class="token punctuation">)</span> <span class="token keyword">as</span> total_ordered
<span class="token keyword">from</span> OrderItems a<span class="token punctuation">,</span> Orders b
<span class="token keyword">where</span> a<span class="token punctuation">.</span>order_num <span class="token operator">=</span> b<span class="token punctuation">.</span>order_num
<span class="token keyword">group</span> <span class="token keyword">by</span> cust_id
<span class="token keyword">order</span> <span class="token keyword">by</span> total_ordered <span class="token keyword">desc</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br></div></div><p>知识点：</p> <ul><li>隐式内连接用 <code>where</code></li> <li>显示内连接用 <code>on</code></li></ul> <h3 id="从-products-表中检索所有的产品名称以及对应的销售总数"><a href="#从-products-表中检索所有的产品名称以及对应的销售总数" class="header-anchor">#</a> 从 Products 表中检索所有的产品名称以及对应的销售总数</h3> <p>Products 表中检索所有的产品名称：prod_name、产品 id：prod_id</p> <table><thead><tr><th>prod_id</th> <th>prod_name</th></tr></thead> <tbody><tr><td>a0001</td> <td>egg</td></tr> <tr><td>a0002</td> <td>sockets</td></tr> <tr><td>a0013</td> <td>coffee</td></tr> <tr><td>a0003</td> <td>cola</td></tr></tbody></table> <p>OrderItems 代表订单商品表，订单产品：prod_id、售出数量：quantity</p> <table><thead><tr><th>prod_id</th> <th>quantity</th></tr></thead> <tbody><tr><td>a0001</td> <td>105</td></tr> <tr><td>a0002</td> <td>1100</td></tr> <tr><td>a0002</td> <td>200</td></tr> <tr><td>a0013</td> <td>1121</td></tr> <tr><td>a0003</td> <td>10</td></tr> <tr><td>a0003</td> <td>19</td></tr> <tr><td>a0003</td> <td>5</td></tr></tbody></table> <p>【问题】</p> <p>编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_name），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity) 检索）。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment"># 写法 1：子查询</span>
<span class="token keyword">SELECT</span> p<span class="token punctuation">.</span>prod_name<span class="token punctuation">,</span> tb<span class="token punctuation">.</span>quant_sold
<span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> prod_id<span class="token punctuation">,</span> <span class="token function">Sum</span><span class="token punctuation">(</span>quantity<span class="token punctuation">)</span> <span class="token keyword">AS</span> quant_sold
    <span class="token keyword">FROM</span> OrderItems
    <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> prod_id<span class="token punctuation">)</span> <span class="token keyword">AS</span> tb<span class="token punctuation">,</span>
  Products p
<span class="token keyword">WHERE</span> tb<span class="token punctuation">.</span>prod_id <span class="token operator">=</span> p<span class="token punctuation">.</span>prod_id

<span class="token comment"># 写法 2：连接表</span>
<span class="token keyword">SELECT</span> p<span class="token punctuation">.</span>prod_name<span class="token punctuation">,</span> <span class="token function">Sum</span><span class="token punctuation">(</span>o<span class="token punctuation">.</span>quantity<span class="token punctuation">)</span> <span class="token keyword">AS</span> quant_sold
<span class="token keyword">FROM</span> Products p<span class="token punctuation">,</span>
  OrderItems o
<span class="token keyword">WHERE</span> p<span class="token punctuation">.</span>prod_id <span class="token operator">=</span> o<span class="token punctuation">.</span>prod_id
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> p<span class="token punctuation">.</span>prod_name（这里不能用 p<span class="token punctuation">.</span>prod_id，会报错）
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><h2 id="联结表"><a href="#联结表" class="header-anchor">#</a> 联结表</h2> <h3 id="返回顾客名称和相关订单号"><a href="#返回顾客名称和相关订单号" class="header-anchor">#</a> 返回顾客名称和相关订单号</h3> <p>Customers 表有字段顾客名称cust_name、顾客id cust_id</p> <table><thead><tr><th>cust_id</th> <th>cust_name</th></tr></thead> <tbody><tr><td>cust10</td> <td>andy</td></tr> <tr><td>cust1</td> <td>ben</td></tr> <tr><td>cust2</td> <td>tony</td></tr> <tr><td>cust22</td> <td>tom</td></tr> <tr><td>cust221</td> <td>an</td></tr> <tr><td>cust2217</td> <td>hex</td></tr></tbody></table> <p>Orders订单信息表，含有字段order_num订单号、cust_id顾客id</p> <table><thead><tr><th>order_num</th> <th>cust_id</th></tr></thead> <tbody><tr><td>a1</td> <td>cust10</td></tr> <tr><td>a2</td> <td>cust1</td></tr> <tr><td>a3</td> <td>cust2</td></tr> <tr><td>a4</td> <td>cust22</td></tr> <tr><td>a5</td> <td>cust221</td></tr> <tr><td>a7</td> <td>cust2217</td></tr></tbody></table> <p>【问题】</p> <p>编写 SQL 语句，返回 Customers 表中的顾客名称（cust_name）和 Orders 表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行升序排序。你可以尝试用两个不同的写法，一个使用简单的等联结语法，另外一个使用 INNER JOIN。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment"># 方法一</span>
<span class="token keyword">select</span> c<span class="token punctuation">.</span>cust_name<span class="token punctuation">,</span> o<span class="token punctuation">.</span>order_num
<span class="token keyword">from</span> Customers c<span class="token punctuation">,</span> Orders o
<span class="token keyword">where</span> c<span class="token punctuation">.</span>cust_id <span class="token operator">=</span> o<span class="token punctuation">.</span>cust_id
<span class="token keyword">order</span> <span class="token keyword">by</span> c<span class="token punctuation">.</span>cust_name

<span class="token comment"># 方法二：inner join</span>
<span class="token keyword">select</span> c<span class="token punctuation">.</span>cust_name<span class="token punctuation">,</span> o<span class="token punctuation">.</span>order_num
<span class="token keyword">from</span> Customers c <span class="token keyword">join</span> Orders o
<span class="token keyword">on</span> c<span class="token punctuation">.</span>cust_id <span class="token operator">=</span> o<span class="token punctuation">.</span>cust_id
<span class="token keyword">order</span> <span class="token keyword">by</span> c<span class="token punctuation">.</span>cust_name
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div><h3 id="返回顾客名称和相关订单号以及每个订单的总价"><a href="#返回顾客名称和相关订单号以及每个订单的总价" class="header-anchor">#</a> 返回顾客名称和相关订单号以及每个订单的总价⭐️</h3> <p>Customers 表有字段，顾客名称：cust_name、顾客 id：cust_id</p> <table><thead><tr><th>cust_id</th> <th>cust_name</th></tr></thead> <tbody><tr><td>cust10</td> <td>andy</td></tr> <tr><td>cust1</td> <td>ben</td></tr> <tr><td>cust2</td> <td>tony</td></tr> <tr><td>cust22</td> <td>tom</td></tr> <tr><td>cust221</td> <td>an</td></tr> <tr><td>cust2217</td> <td>hex</td></tr></tbody></table> <p>Orders 订单信息表，含有字段，订单号：order_num、顾客 id：cust_id</p> <table><thead><tr><th>order_num</th> <th>cust_id</th></tr></thead> <tbody><tr><td>a1</td> <td>cust10</td></tr> <tr><td>a2</td> <td>cust1</td></tr> <tr><td>a3</td> <td>cust2</td></tr> <tr><td>a4</td> <td>cust22</td></tr> <tr><td>a5</td> <td>cust221</td></tr> <tr><td>a7</td> <td>cust2217</td></tr></tbody></table> <p>OrderItems 表有字段，商品订单号：order_num、商品数量：quantity、商品价格：item_price</p> <table><thead><tr><th>order_num</th> <th>quantity</th> <th>item_price</th></tr></thead> <tbody><tr><td>a1</td> <td>1000</td> <td>10</td></tr> <tr><td>a2</td> <td>200</td> <td>10</td></tr> <tr><td>a3</td> <td>10</td> <td>15</td></tr> <tr><td>a4</td> <td>25</td> <td>50</td></tr> <tr><td>a5</td> <td>15</td> <td>25</td></tr> <tr><td>a7</td> <td>7</td> <td>7</td></tr></tbody></table> <p>【问题】除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和 Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment"># 等连接语法</span>
<span class="token keyword">select</span> c<span class="token punctuation">.</span>cust_name<span class="token punctuation">,</span> o<span class="token punctuation">.</span>order_num<span class="token punctuation">,</span> <span class="token function">sum</span><span class="token punctuation">(</span>o2<span class="token punctuation">.</span>quantity <span class="token operator">*</span> o2<span class="token punctuation">.</span>item_price<span class="token punctuation">)</span> <span class="token keyword">as</span> OrderTotal
<span class="token keyword">from</span> Customers c<span class="token punctuation">,</span> Orders o<span class="token punctuation">,</span> OrderItems o2
<span class="token keyword">where</span> c<span class="token punctuation">.</span>cust_id <span class="token operator">=</span> o<span class="token punctuation">.</span>cust_id <span class="token operator">and</span> o<span class="token punctuation">.</span>order_num <span class="token operator">=</span> o2<span class="token punctuation">.</span>order_num
<span class="token keyword">group</span> <span class="token keyword">by</span> c<span class="token punctuation">.</span>cust_name<span class="token punctuation">,</span> o<span class="token punctuation">.</span>order_num
<span class="token keyword">order</span> <span class="token keyword">by</span> c<span class="token punctuation">.</span>cust_name<span class="token punctuation">,</span> o<span class="token punctuation">.</span>order_num
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>知识点：</p> <ul><li>分组：通过在 <code>GROUP BY</code> 子句中使用 <code>c.cust_name, o.order_num</code> 来指定按 <code>Customers</code> 表中的 <code>cust_name</code> 和 <code>Orders</code> 表中的 <code>order_num</code> 对查询结果进行分组。这样，查询结果将按照顾客姓名和订单号进行分组，每个分组对应一个顾客的一个订单。（唯一的）</li></ul> <p>分组的目的是将具有相同 <code>cust_name</code> 和 <code>order_num</code> 字段值的数据行归为一组，并对每个组内的数据进行聚合操作。</p> <blockquote><p>一句话，select 中的字段要么都聚类，要么都不聚类</p></blockquote> <p><em>是否要分组取决于查询的需求。</em></p> <p>如果不需要对数据进行聚合操作或按照某些字段进行统计计算，那么可以不使用分组。但在这个查询中，由于需要计算每个订单的总金额，所以需要按照 <code>cust_name</code> 和 <code>order_num</code> 字段进行分组。如果不使用 <code>GROUP BY</code>，查询结果将会是没有经过聚合计算的所有数据行，而不是按订单进行汇总后的结果。</p> <h3 id="确定哪些订单购买了-prod-id-为-br01-的产品-二"><a href="#确定哪些订单购买了-prod-id-为-br01-的产品-二" class="header-anchor">#</a> 确定哪些订单购买了 prod_id 为 BR01 的产品（二）</h3> <p>表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date</p> <p>OrderItems 表</p> <table><thead><tr><th>prod_id</th> <th>order_num</th></tr></thead> <tbody><tr><td>BR01</td> <td>a0001</td></tr> <tr><td>BR01</td> <td>a0002</td></tr> <tr><td>BR02</td> <td>a0003</td></tr> <tr><td>BR02</td> <td>a0013</td></tr></tbody></table> <p>Orders 表</p> <table><thead><tr><th>order_num</th> <th>cust_id</th> <th>order_date</th></tr></thead> <tbody><tr><td>a0001</td> <td>cust10</td> <td>2022-01-01 00:00:00</td></tr> <tr><td>a0002</td> <td>cust1</td> <td>2022-01-01 00:01:00</td></tr> <tr><td>a0003</td> <td>cust1</td> <td>2022-01-02 00:00:00</td></tr> <tr><td>a0013</td> <td>cust2</td> <td>2022-01-01 00:20:00</td></tr></tbody></table> <p>【问题】编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 &quot;BR01&quot; 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> cust_id<span class="token punctuation">,</span> order_date
<span class="token keyword">from</span> Orders
<span class="token keyword">where</span> order_num <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> order_num
                    <span class="token keyword">from</span> OrderItems
                    <span class="token keyword">where</span> prod_id <span class="token operator">=</span> <span class="token string">'BR01'</span><span class="token punctuation">)</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> order_date
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><h3 id="返回购买-prod-id-为-br01-的产品的所有顾客的电子邮件-二"><a href="#返回购买-prod-id-为-br01-的产品的所有顾客的电子邮件-二" class="header-anchor">#</a> 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）</h3> <p>有表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date；Customers 表含有 cust_email 顾客邮件和 cust_id 顾客 id</p> <p>OrderItems 表</p> <table><thead><tr><th>prod_id</th> <th>order_num</th></tr></thead> <tbody><tr><td>BR01</td> <td>a0001</td></tr> <tr><td>BR01</td> <td>a0002</td></tr> <tr><td>BR02</td> <td>a0003</td></tr> <tr><td>BR02</td> <td>a0013</td></tr></tbody></table> <p>Orders表</p> <table><thead><tr><th>order_num</th> <th>cust_id</th> <th>order_date</th></tr></thead> <tbody><tr><td>a0001</td> <td>cust10</td> <td>2022-01-01 00:00:00</td></tr> <tr><td>a0002</td> <td>cust1</td> <td>2022-01-01 00:01:00</td></tr> <tr><td>a0003</td> <td>cust1</td> <td>2022-01-02 00:00:00</td></tr> <tr><td>a0013</td> <td>cust2</td> <td>2022-01-01 00:20:00</td></tr></tbody></table> <p>Customers 表代表顾客信息，cust_id 为顾客 id，cust_email 为顾客 email</p> <p>【问题】返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。</p> <p>提示：涉及到 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id，但是必须使用 INNER JOIN 语法。(我看未必)</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> cust_email
<span class="token keyword">from</span> Customers 
<span class="token keyword">where</span> cust_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> cust_id
                    <span class="token keyword">from</span> Orders
                    <span class="token keyword">where</span> order_num <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> order_num
                                        <span class="token keyword">from</span> OrderItems
                                        <span class="token keyword">where</span> prod_id <span class="token operator">=</span> <span class="token string">'BR01'</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><h3 id="确定最佳顾客的另一种方式-二"><a href="#确定最佳顾客的另一种方式-二" class="header-anchor">#</a> 确定最佳顾客的另一种方式（二）⭐️</h3> <p>OrderItems 表代表订单信息，确定最佳顾客的另一种方式是看他们花了多少钱，OrderItems 表有订单号 order_num 和 item_price 商品售出价格、quantity 商品数量</p> <table><thead><tr><th>order_num</th> <th>item_price</th> <th>quantity</th></tr></thead> <tbody><tr><td>a1</td> <td>10</td> <td>105</td></tr> <tr><td>a2</td> <td>1</td> <td>1100</td></tr> <tr><td>a2</td> <td>1</td> <td>200</td></tr> <tr><td>a4</td> <td>2</td> <td>1121</td></tr> <tr><td>a5</td> <td>5</td> <td>10</td></tr> <tr><td>a2</td> <td>1</td> <td>19</td></tr> <tr><td>a7</td> <td>7</td> <td>5</td></tr></tbody></table> <p>Orders 表含有字段 order_num 订单号、cust_id 顾客 id</p> <table><thead><tr><th>order_num</th> <th>cust_id</th></tr></thead> <tbody><tr><td>a1</td> <td>cust10</td></tr> <tr><td>a2</td> <td>cust1</td></tr> <tr><td>a3</td> <td>cust2</td></tr> <tr><td>a4</td> <td>cust22</td></tr> <tr><td>a5</td> <td>cust221</td></tr> <tr><td>a7</td> <td>cust2217</td></tr></tbody></table> <p>顾客表 Customers 有字段 cust_id 客户 id、cust_name 客户姓名</p> <table><thead><tr><th>cust_id</th> <th>cust_name</th></tr></thead> <tbody><tr><td>cust10</td> <td>andy</td></tr> <tr><td>cust1</td> <td>ben</td></tr> <tr><td>cust2</td> <td>tony</td></tr> <tr><td>cust22</td> <td>tom</td></tr> <tr><td>cust221</td> <td>an</td></tr> <tr><td>cust2217</td> <td>hex</td></tr></tbody></table> <p>【问题】编写 SQL 语句，返回订单总价不小于1000 的客户名称和总额（OrderItems 表中的order_num）。</p> <p>提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用 INNER JOIN 语法。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> cust_name<span class="token punctuation">,</span> <span class="token function">sum</span><span class="token punctuation">(</span>item_price <span class="token operator">*</span> quantity<span class="token punctuation">)</span> <span class="token keyword">as</span> total_price
<span class="token keyword">from</span> Customers
<span class="token keyword">inner</span> <span class="token keyword">join</span> Orders <span class="token keyword">using</span><span class="token punctuation">(</span>cust_id<span class="token punctuation">)</span>
<span class="token keyword">inner</span> <span class="token keyword">join</span> OrderItems <span class="token keyword">using</span><span class="token punctuation">(</span>order_num<span class="token punctuation">)</span>
<span class="token keyword">group</span> <span class="token keyword">by</span> cust_name
<span class="token keyword">having</span> total_price <span class="token operator">&gt;=</span> <span class="token number">1000</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> total_price
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>知识点：</p> <p>通过 <code>using</code> 关键字，根据 <code>cust_id</code> 将 <code>Customers</code> 表和 <code>Orders</code> 表连接，根据 <code>order_num</code> 将 <code>Orders</code> 表和 <code>OrderItems</code> 表连接。</p> <p><em>using 与 on 区别</em></p> <p><code>using</code> 和 <code>on</code> 是在 SQL 中用于连接表的两种不同方式。</p> <ol><li><code>using</code>：</li></ol> <ul><li><code>using</code> 是一种简化的连接语法，用于连接两个表，并指定连接条件的字段。它适用于连接那些具有相同名称的字段的表。</li> <li>在使用 <code>using</code> 进行连接时，只需在连接的表名之间使用 <code>using</code> 关键字，并在后面紧跟需要进行连接的字段名，这些字段必须在两个表中都存在且名称相同。</li> <li>例如：<code>INNER JOIN table2 using(column_name)</code>。</li></ul> <ol start="2"><li><code>on</code>：</li></ol> <ul><li><code>on</code> 是一种更灵活的连接语法，用于连接两个表，并指定连接条件的具体条件。它适用于连接那些具有不同名称或者复杂条件的字段的表。</li> <li>在使用 <code>on</code> 进行连接时，需要在 <code>on</code> 关键字后面写入连接条件，连接条件可以是简单的等值条件，也可以是更复杂的逻辑条件。</li> <li>例如：<code>INNER JOIN table2 on table1.column_name = table2.column_name</code>。</li></ul> <p>总结：</p> <ul><li><code>using</code> 适用于连接具有<strong>相同名称的字段</strong>的表，它简洁且适用于某些特定场景。</li> <li><code>on</code> 更灵活，适用于连接具有<strong>不同名称或复杂条件的字段</strong>的表，它提供了更多的连接方式和条件控制。</li></ul> <h2 id="创建高级联结"><a href="#创建高级联结" class="header-anchor">#</a> 创建高级联结</h2> <h3 id="检索每个顾客的名称和所有的订单号-一"><a href="#检索每个顾客的名称和所有的订单号-一" class="header-anchor">#</a> 检索每个顾客的名称和所有的订单号（一）</h3> <p>Customers 表代表顾客信息含有顾客 id cust_id 和 顾客名称 cust_name</p> <table><thead><tr><th>cust_id</th> <th>cust_name</th></tr></thead> <tbody><tr><td>cust10</td> <td>andy</td></tr> <tr><td>cust1</td> <td>ben</td></tr> <tr><td>cust2</td> <td>tony</td></tr> <tr><td>cust22</td> <td>tom</td></tr> <tr><td>cust221</td> <td>an</td></tr> <tr><td>cust2217</td> <td>hex</td></tr></tbody></table> <p>Orders表代表订单信息含有订单号order_num和顾客id cust_id</p> <table><thead><tr><th>order_num</th> <th>cust_id</th></tr></thead> <tbody><tr><td>a1</td> <td>cust10</td></tr> <tr><td>a2</td> <td>cust1</td></tr> <tr><td>a3</td> <td>cust2</td></tr> <tr><td>a4</td> <td>cust22</td></tr> <tr><td>a5</td> <td>cust221</td></tr> <tr><td>a7</td> <td>cust2217</td></tr></tbody></table> <p>【问题】使用 INNER JOIN 编写 SQL 语句，检索每个顾客的名称（Customers 表中的 cust_name）和所有的订单号（Orders 表中的 order_num），最后根据顾客姓名 cust_name 升序返回。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> cust_name<span class="token punctuation">,</span> order_num
<span class="token keyword">from</span> Customers
<span class="token keyword">inner</span> <span class="token keyword">join</span> Orders <span class="token keyword">using</span><span class="token punctuation">(</span>cust_id<span class="token punctuation">)</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> cust_name
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="检索每个顾客的名称和所有的订单号-二"><a href="#检索每个顾客的名称和所有的订单号-二" class="header-anchor">#</a> 检索每个顾客的名称和所有的订单号（二）⭐️</h3> <p>Orders 表代表订单信息含有订单号 order_num 和顾客 id：cust_id</p> <table><thead><tr><th>order_num</th> <th>cust_id</th></tr></thead> <tbody><tr><td>a1</td> <td>cust10</td></tr> <tr><td>a2</td> <td>cust1</td></tr> <tr><td>a3</td> <td>cust2</td></tr> <tr><td>a4</td> <td>cust22</td></tr> <tr><td>a5</td> <td>cust221</td></tr> <tr><td>a7</td> <td>cust2217</td></tr></tbody></table> <p>Customers 表代表顾客信息含有顾客 id cust_id 和顾客名称 cust_name</p> <table><thead><tr><th>cust_id</th> <th>cust_name</th></tr></thead> <tbody><tr><td>cust10</td> <td>andy</td></tr> <tr><td>cust1</td> <td>ben</td></tr> <tr><td>cust2</td> <td>tony</td></tr> <tr><td>cust22</td> <td>tom</td></tr> <tr><td>cust221</td> <td>an</td></tr> <tr><td>cust2217</td> <td>hex</td></tr> <tr><td>cust40</td> <td>ace</td></tr></tbody></table> <p>【问题】检索每个顾客的名称（Customers 表中的 cust_name）和所有的订单号（Orders 表中的 order_num），<strong>列出所有的顾客，即使他们没有下过订单</strong>。最后根据顾客姓名 cust_name 升序返回。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> cust_name<span class="token punctuation">,</span>order_num
<span class="token keyword">from</span> Customers
<span class="token keyword">left</span> <span class="token keyword">join</span> Orders <span class="token keyword">using</span><span class="token punctuation">(</span>cust_id<span class="token punctuation">)</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> cust_name<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>知识点：</p> <ol><li><p>内联结：inner join。取两列的交集。</p></li> <li><p>外联结：</p> <ul><li><p>left join。左连接，以左边表的列为主，取两列的交集，对于不在右边列存在的名称取 null。</p></li> <li><p>right join。右连接，以右边表的列为主，取两列的交集，对于不在左边列存在的名称取 null。</p></li></ul></li></ol> <h3 id="返回产品名称和与之相关的订单号"><a href="#返回产品名称和与之相关的订单号" class="header-anchor">#</a> 返回产品名称和与之相关的订单号</h3> <p>Products 表为产品信息表含有字段 prod_id 产品 id、prod_name 产品名称</p> <table><thead><tr><th>prod_id</th> <th>prod_name</th></tr></thead> <tbody><tr><td>a0001</td> <td>egg</td></tr> <tr><td>a0002</td> <td>sockets</td></tr> <tr><td>a0013</td> <td>coffee</td></tr> <tr><td>a0003</td> <td>cola</td></tr> <tr><td>a0023</td> <td>soda</td></tr></tbody></table> <p>OrderItems 表为订单信息表含有字段 order_num 订单号和产品 id prod_id</p> <table><thead><tr><th>prod_id</th> <th>order_num</th></tr></thead> <tbody><tr><td>a0001</td> <td>a105</td></tr> <tr><td>a0002</td> <td>a1100</td></tr> <tr><td>a0002</td> <td>a200</td></tr> <tr><td>a0013</td> <td>a1121</td></tr> <tr><td>a0003</td> <td>a10</td></tr> <tr><td>a0003</td> <td>a19</td></tr> <tr><td>a0003</td> <td>a5</td></tr></tbody></table> <p>【问题】使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和与之相关的订单号（order_num）的列表，并按照产品名称升序排序。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> prod_name<span class="token punctuation">,</span>order_num
<span class="token keyword">from</span> Products
<span class="token keyword">left</span> <span class="token keyword">join</span> OrderItems <span class="token keyword">using</span><span class="token punctuation">(</span>prod_id<span class="token punctuation">)</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> prod_name<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="返回产品名称和每一项产品的总订单数"><a href="#返回产品名称和每一项产品的总订单数" class="header-anchor">#</a> 返回产品名称和每一项产品的总订单数</h3> <p>Products 表为产品信息表含有字段 prod_id 产品 id、prod_name 产品名称</p> <table><thead><tr><th>prod_id</th> <th>prod_name</th></tr></thead> <tbody><tr><td>a0001</td> <td>egg</td></tr> <tr><td>a0002</td> <td>sockets</td></tr> <tr><td>a0013</td> <td>coffee</td></tr> <tr><td>a0003</td> <td>cola</td></tr> <tr><td>a0023</td> <td>soda</td></tr></tbody></table> <p>OrderItems 表为订单信息表含有字段 order_num 订单号和产品 id prod_id</p> <table><thead><tr><th>prod_id</th> <th>order_num</th></tr></thead> <tbody><tr><td>a0001</td> <td>a105</td></tr> <tr><td>a0002</td> <td>a1100</td></tr> <tr><td>a0002</td> <td>a200</td></tr> <tr><td>a0013</td> <td>a1121</td></tr> <tr><td>a0003</td> <td>a10</td></tr> <tr><td>a0003</td> <td>a19</td></tr> <tr><td>a0003</td> <td>a5</td></tr></tbody></table> <p>【问题】使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回<strong>产品名称</strong>（prod_name）和每一项产品的总订单数（不是订单号），并按产品名称升序排序。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment"># select prod_name, count(order_num) as orders</span>
<span class="token keyword">select</span> prod_name<span class="token punctuation">,</span> <span class="token function">count</span><span class="token punctuation">(</span>o<span class="token punctuation">.</span>prod_id<span class="token punctuation">)</span> <span class="token keyword">as</span> orders
<span class="token keyword">from</span> Products
<span class="token keyword">left</span> <span class="token keyword">join</span> OrderItems o <span class="token keyword">using</span><span class="token punctuation">(</span>prod_id<span class="token punctuation">)</span>
<span class="token keyword">group</span> <span class="token keyword">by</span> prod_name
<span class="token keyword">order</span> <span class="token keyword">by</span> prod_name<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>知识点：</p> <ul><li><p>使用 COUNT() 聚合函数计算每个产品的总订单数，并将结果作为 total_orders 列的值返回。</p></li> <li><p>有聚合函数就要分组。</p></li></ul> <h3 id="列出供应商及其可供产品的数量"><a href="#列出供应商及其可供产品的数量" class="header-anchor">#</a> 列出供应商及其可供产品的数量</h3> <p>有 Vendors 表含有 vend_id 供应商 id.</p> <table><thead><tr><th>vend_id</th></tr></thead> <tbody><tr><td>a0002</td></tr> <tr><td>a0013</td></tr> <tr><td>a0003</td></tr> <tr><td>a0010</td></tr></tbody></table> <p>有 Products 表含有供应商 id 和供应产品 id</p> <table><thead><tr><th>vend_id</th> <th>prod_id</th></tr></thead> <tbody><tr><td>a0001</td> <td>egg</td></tr> <tr><td>a0002</td> <td>prod_id_iphone</td></tr> <tr><td>a00113</td> <td>prod_id_tea</td></tr> <tr><td>a0003</td> <td>prod_id_vivo phone</td></tr> <tr><td>a0010</td> <td>prod_id_huawei phone</td></tr></tbody></table> <p>【问题】</p> <p>列出供应商（Vendors 表中的 vend_id）及其可供产品的数量，包括没有产品的供应商。你需要使用 OUTER JOIN 和 COUNT() 聚合函数来计算 Products 表中每种产品的数量，最后根据 vend_id 升序排序。</p> <p>注意：vend_id 列会显示在多个表中，因此在每次引用它时都需要完全限定它。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> v<span class="token punctuation">.</span>vend_id<span class="token punctuation">,</span> <span class="token function">count</span><span class="token punctuation">(</span>p<span class="token punctuation">.</span>prod_id<span class="token punctuation">)</span> <span class="token keyword">as</span> prod_id
<span class="token keyword">from</span> Vendors v
<span class="token keyword">left</span> <span class="token keyword">join</span> Products p <span class="token keyword">using</span><span class="token punctuation">(</span>vend_id<span class="token punctuation">)</span>
<span class="token keyword">group</span> <span class="token keyword">by</span> v<span class="token punctuation">.</span>vend_id
<span class="token keyword">order</span> <span class="token keyword">by</span> v<span class="token punctuation">.</span>vend_id<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h2 id="组合查询"><a href="#组合查询" class="header-anchor">#</a> 组合查询</h2> <h3 id="将两个-select-语句结合起来-一"><a href="#将两个-select-语句结合起来-一" class="header-anchor">#</a> 将两个 SELECT 语句结合起来（一）</h3> <p>表 OrderItems 包含订单产品信息，字段 prod_id 代表产品 id、quantity 代表产品数量</p> <table><thead><tr><th>prod_id</th> <th>quantity</th></tr></thead> <tbody><tr><td>a0001</td> <td>105</td></tr> <tr><td>a0002</td> <td>100</td></tr> <tr><td>a0002</td> <td>200</td></tr> <tr><td>a0013</td> <td>1121</td></tr> <tr><td>a0003</td> <td>10</td></tr> <tr><td>a0003</td> <td>19</td></tr> <tr><td>a0003</td> <td>5</td></tr> <tr><td>BNBG</td> <td>10002</td></tr></tbody></table> <p>【问题】将两个 SELECT 语句结合起来，以便从 OrderItems 表中检索产品 id（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> prod_id<span class="token punctuation">,</span> quantity
<span class="token keyword">from</span> OrderItems
<span class="token keyword">where</span> quantity <span class="token operator">=</span> <span class="token number">100</span>
<span class="token keyword">union</span>
<span class="token keyword">select</span> prod_id<span class="token punctuation">,</span> quantity
<span class="token keyword">from</span> OrderItems
<span class="token keyword">where</span> prod_id <span class="token operator">like</span> <span class="token string">'BNBG%'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>知识点：</p> <p><a href="https://cmty256.github.io/pages/ed0fa0/#%E7%BB%84%E5%90%88-union" target="_blank" rel="noopener noreferrer">UNION 组合 | DreamRain (cmty256.github.io)<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p> <h3 id="将两个-select-语句结合起来-二"><a href="#将两个-select-语句结合起来-二" class="header-anchor">#</a> 将两个 SELECT 语句结合起来（二）</h3> <p>表 OrderItems 包含订单产品信息，字段 prod_id 代表产品 id、quantity 代表产品数量。</p> <table><thead><tr><th>prod_id</th> <th>quantity</th></tr></thead> <tbody><tr><td>a0001</td> <td>105</td></tr> <tr><td>a0002</td> <td>100</td></tr> <tr><td>a0002</td> <td>200</td></tr> <tr><td>a0013</td> <td>1121</td></tr> <tr><td>a0003</td> <td>10</td></tr> <tr><td>a0003</td> <td>19</td></tr> <tr><td>a0003</td> <td>5</td></tr> <tr><td>BNBG</td> <td>10002</td></tr></tbody></table> <p>【问题】将两个 SELECT 语句结合起来，以便从 OrderItems 表中检索产品 id（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。
<strong>注意：这次仅使用单个 SELECT 语句。</strong></p> <p>【示例结果】</p> <p>返回产品id prod_id 和产品数量 quantity</p> <table><thead><tr><th>prod_id</th> <th>quantity</th></tr></thead> <tbody><tr><td>a0002</td> <td>100</td></tr> <tr><td>BNBG</td> <td>10002</td></tr></tbody></table> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> prod_id<span class="token punctuation">,</span> quantity
<span class="token keyword">from</span> OrderItems
<span class="token keyword">where</span> quantity <span class="token operator">=</span> <span class="token number">100</span> <span class="token operator">or</span> prod_id <span class="token operator">like</span> <span class="token string">'BNBG%'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="组合-products-表中的产品名称和-customers-表中的顾客名称"><a href="#组合-products-表中的产品名称和-customers-表中的顾客名称" class="header-anchor">#</a> 组合 Products 表中的产品名称和 Customers 表中的顾客名称⭐️</h3> <p>Products 表含有字段 prod_name 代表产品名称</p> <table><thead><tr><th>prod_name</th></tr></thead> <tbody><tr><td>flower</td></tr> <tr><td>rice</td></tr> <tr><td>ring</td></tr> <tr><td>umbrella</td></tr></tbody></table> <p>Customers 表代表顾客信息，cust_name 代表顾客名称</p> <table><thead><tr><th>cust_name</th></tr></thead> <tbody><tr><td>andy</td></tr> <tr><td>ben</td></tr> <tr><td>tony</td></tr> <tr><td>tom</td></tr> <tr><td>an</td></tr> <tr><td>lee</td></tr> <tr><td>hex</td></tr></tbody></table> <p>【问题】</p> <p>编写 SQL 语句，组合 Products 表中的产品名称（prod_name）和 Customers 表中的顾客名称（cust_name）并返回，然后按产品名称对结果进行升序排序。</p> <p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment"># UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名.</span>
<span class="token keyword">select</span> prod_name
<span class="token keyword">from</span> Products
<span class="token keyword">union</span>
<span class="token keyword">select</span> cust_name
<span class="token keyword">from</span> Customers
<span class="token keyword">order</span> <span class="token keyword">by</span> prod_name
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><h3 id="纠错-4"><a href="#纠错-4" class="header-anchor">#</a> 纠错 4</h3> <p>表 Customers 含有字段 cust_name 顾客名、cust_contact 顾客联系方式、cust_state 顾客州、cust_email 顾客 email</p> <p><img src="https://uploadfiles.nowcoder.com/images/20220219/393256_1645261550253/F36BDA89E8A58FA742AAFE2284C61EB3" alt="img"></p> <p>【问题】修正下面错误的 SQL</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> cust_name<span class="token punctuation">,</span> cust_contact<span class="token punctuation">,</span> cust_email 
<span class="token keyword">FROM</span> Customers 
<span class="token keyword">WHERE</span> cust_state <span class="token operator">=</span> <span class="token string">'MI'</span> 
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> cust_name<span class="token punctuation">;</span> 
<span class="token keyword">UNION</span> 
<span class="token keyword">SELECT</span> cust_name<span class="token punctuation">,</span> cust_contact<span class="token punctuation">,</span> cust_email 
<span class="token keyword">FROM</span> Customers 
<span class="token keyword">WHERE</span> cust_state <span class="token operator">=</span> <span class="token string">'IL'</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> cust_name<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> cust_name<span class="token punctuation">,</span> cust_contact<span class="token punctuation">,</span> cust_email 
<span class="token keyword">FROM</span> Customers 
<span class="token keyword">WHERE</span> cust_state <span class="token operator">=</span> <span class="token string">'MI'</span> 
<span class="token keyword">UNION</span> 
<span class="token keyword">SELECT</span> cust_name<span class="token punctuation">,</span> cust_contact<span class="token punctuation">,</span> cust_email 
<span class="token keyword">FROM</span> Customers 
<span class="token keyword">WHERE</span> cust_state <span class="token operator">=</span> <span class="token string">'IL'</span> 
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> cust_name<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><p>错误点：写了两个 <code>order by</code></p> <p>知识点：</p> <p>使用 union 组合查询时，只能使用一条 order by 子句，他必须位于最后一条 select 语句之后，因为对于【结果集】不存在对于一部分数据进行排序，而另一部分用另一种排序规则的情况。</p></div></div> <!----> <div class="page-edit"><!----> <!----> <!----></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/pages/ed0fa0/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">SQL语法基础小结</div></a> <a href="/pages/ccc445/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">MySQL基础小结</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/pages/ed0fa0/" class="prev">SQL语法基础小结</a></span> <span class="next"><a href="/pages/ccc445/">MySQL基础小结</a>→
      </span></p></div></div></div> <!----></main></div> <div class="footer"><!----> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2023-2023
    <span>霜晨月</span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"><canvas id="vuepress-canvas-cursor"></canvas></div></div>
    <script src="/assets/js/app.ef25b098.js" defer></script><script src="/assets/js/2.4615a819.js" defer></script><script src="/assets/js/69.6d87bcc0.js" defer></script>
  </body>
</html>
